SAC Data Action - are IF statements like a select on the data and then the if block is executed?

[axel_]

Hello Planning Gurus,

I got a general question on how the data is processed in data actions. Coming from BW my thinking was that a data action loops thru the data and executes on every record the data action code sequentially (like a BW start routine would do).

However, recently code of one data action acted weired (see long text bellow) and I wonder if I was thinking wrong all the time and there is no overall loop but the code is simply executed sequentially and is resulting in selection based loops. 

So your memberset makes the first select. If you have an if statement for a particular month this is then selected from the memberset result and the if statement block is executed on that selected data (now in a loop) and written back if there is any data() command.

Next if statement is again selecting data from the memberset result (could overlapp with previous if statement) and executed...

Is this how an data action works? 

Thanks and best regards
Axel

Data Action Problem that triggered the question in regards to processing
---------------------------------------

I got a data action that is calculating the difference Q1 to Q1 previous year using variablemembers for a monthly planning model.

MEMBERSET [d/Measures] = ("I_GROWTH")

MEMBERSET [d/Monat] = BASEMEMBER([d/Monat].[h/YM],%YEAR_TO_CALCULATE%  )

AGGREGATE_DIMENSIONS = ....

AGGREGATE_WRITETO .... = "#"

AGGREGATE_WRITETO .... = "#"

AGGREGATE_WRITETO .... = "#"

AGGREGATE_WRITETO .... = "#"

AGGREGATE_WRITETO .... = "#"

AGGREGATE_WRITETO .... = "#"

VARIABLEMEMBER #QTR_VARIANCE_Q1 OF [d/Measures]

VARIABLEMEMBER #QTR_Q1 OF [d/Monat]
VARIABLEMEMBER #QTR_VARIANCE_Q2 OF [d/Measures]
VARIABLEMEMBER #QTR_Q2 OF [d/Monat]
VARIABLEMEMBER #QTR_VARIANCE_Q3 OF [d/Measures]
VARIABLEMEMBER #QTR_Q3 OF [d/Monat]

VARIABLEMEMBER #QTR_VARIANCE_Q4 OF [d/Measures]

VARIABLEMEMBER #QTR_Q4 OF [d/Monat]

DELETE([d/Measures]="I_GROWTH")

IF [d/Monat].[p/QUARTERDESC]="Q1" THEN

   DATA([d/Measures]=#QTR_VARIANCE_Q1,[d/Monat]=#QTR_Q1)=RESULTLOOKUP([d/Measures]="REV")+       (RESULTLOOKUP([d/Version]=%REF_VERSION_Q1Q2%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)

ENDIF

// Write growth bonus to last month of qtr

IF [d/Monat].[p/MONTHDESC]="Mar" THEN

    IF (RESULTLOOKUP([d/Measures]=#QTR_VARIANCE_Q1,[d/Monat]=#QTR_Q1)) >= 1000 THEN

    DATA()=RESULTLOOKUP([d/Measures]=#QTR_VARIANCE_Q1,[d/Monat]=#QTR_Q1)*3.00

ENDIF

ENDIF

This is working fine. Requirement changed now that growth for Q4 should be compared only Nov and Dec (not the full qrt) so I adjusted the code to check for month

IF [d/Monat].[p/MONTHDESC]="Nov" THEN
DATA([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)=RESULTLOOKUP([d/Measures]="REV")+(RESULTLOOKUP([d/Version]=%REF_VERSION_Q3Q4%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)
ENDIF
IF [d/Monat].[p/MONTHDESC]="Dec" THEN
 DATA([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)=RESULTLOOKUP([d/Measures]="REV")+ (RESULTLOOKUP([d/Version]=%REF_VERSION_Q3Q4%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)

 IF (RESULTLOOKUP([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)) >= 700 THEN
 DATA()=RESULTLOOKUP([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)*3.00
ENDIF
ENDIF

Problem: the if statement for November seems to be executed after Dec. Hence the QTR_VARIANCE variable holds only difference Dec to Dec and therfore the growth bonus is calculated to low. (at least that is my only explanation). The Nov statement is definitly executed I checked that by writing some data to Nov month.

When I changed code to 
IF [d/Monat].[p/MONTHDESC]="Nov" OR [d/Monat].[p/MONTHDESC]="Dec" THEN
DATA([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)=RESULTLOOKUP([d/Measures]="REV")+(RESULTLOOKUP([d/Version]=%REF_VERSION_Q3Q4%,[d/Measures]="PAX",[d/Monat]=PREVIOUS(12))*-1)

ENDIF
IF [d/Monat].[p/MONTHDESC]="Dec" THEN
 IF (RESULTLOOKUP([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)) >= 700 THEN
 DATA()=RESULTLOOKUP([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)*3.00
 ENDIF
ENDIF

It was working as expected.

[N1kh1l · Accepted solution]

@axel_ 

I think its the they way SAC aggregates the result. When you say 

IF [d/Monat].[p/QUARTERDESC]="Q1" THEN

   DATA([d/Measures]=#QTR_VARIANCE_Q1,[d/Monat]=#QTR_Q1)=RESULTLOOKUP([d/Measures]="REV")+       (RESULTLOOKUP([d/Version]=%REF_VERSION_Q1Q2%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)

ENDIF

 this IF evaluates to true for January February and march and the difference of each month from last year is aggregated as they are within same IF block and written to [d/Measures]=#QTR_VARIANCE_Q1,[d/Monat]=#QTR_Q1

Now coming to this one . Remember SAC evaluates statement sequentially

IF [d/Monat].[p/MONTHDESC]="Nov" THEN
DATA([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)=RESULTLOOKUP([d/Measures]="REV")+(RESULTLOOKUP([d/Version]=%REF_VERSION_Q3Q4%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)
ENDIF
IF [d/Monat].[p/MONTHDESC]="Dec" THEN
 DATA([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)=RESULTLOOKUP([d/Measures]="REV")+ (RESULTLOOKUP([d/Version]=%REF_VERSION_Q3Q4%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)

The December calculation will overwrite the November calculation because of the sequence and they being separate IF block but same write intersection [d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4

So either do DATA.APPEND() for December IF Block or change your IF condition to 

IF [d/Monat].[p/MONTHDESC]="Nov"  OR IF [d/Monat].[p/MONTHDESC]="Dec" THEN
DATA([d/Measures]=#QTR_VARIANCE_Q4,[d/Monat]=#QTR_Q4)=RESULTLOOKUP([d/Measures]="REV")+(RESULTLOOKUP([d/Version]=%REF_VERSION_Q3Q4%,[d/Measures]="REV",[d/Monat]=PREVIOUS(12))*-1)
ENDIF

Hope this helps !!

Nikhil

[axel_ · Accepted solution]

Hello Nikhil,

thank you very much for your reply.

Best regards
Axel