Calculate current month basen on cumulative sums

[damirm86]

Hi

In my attached file you see 3 dimensions and one cumulative value. What I need to do is to calculate a value per month (current vs prev). This needs to be done in the load script. Any suggestions?

[aarkay29 · Accepted solution]

TRY THIS

A:

LOAD FY,

     YM,

     BU_CODE_SUP,

     NOTI_VAL_EURO

FROM

(biff, embedded labels, table is Sheet1$)

wHERE NOT ISNULL(BU_CODE_SUP);

NoConcatenate

lOAD * ,

iF(PEEK(BU_CODE_SUP)=BU_CODE_SUP,NOTI_VAL_EURO-PEEK('NOTI_VAL_EURO'),NOTI_VAL_EURO) AS F5

RESIDENT A

Order bY  

     BU_CODE_SUP,YM;

DROP TABLE A;

[effinty2112]

Hi Aar,

               This one is a bit trickier than it looks. Using Peek might work but suppose a code has a value for 201704, no value for 201705 and then a value for 201706. If 201705 had a value of zero then we might be okay but no value would be a problem.

cheers

Andrew

[aarkay29]

That really depends on how the data is...

I just gave the sample code for what his current data is and will have to be modified based on the right scenario

[damirm86]

Workss very good. Thanks!