How to subtract a KF value from another KF from multiple periods.

[bindhu_konidala]

Hello Experts,

I have two key figures KF1 & KF2 with values like below.

I want to subtract KF1 value (100) from KF2 from all the next periods.

Here it should zero out Apr,May,Jun and from July month it should minus 10 and the value of July month should be 40.

Requirement is to subtract the value of KF1 starting from Current month to future from KF2.

Please suggest.

Thanks in advance.

[rahulmohnot · Accepted solution]

Hi Bindhu,

A little complex approach but this should work:

CummKF2 = IBP_RAGGR ("KF2@PL", ''SUM'', 0, N, ''CURRENTFUTURE'')

CummKF1 = IBP_RAGGR ("KF1@PL", ''SUM'', 0, "N", ''CURRENTFUTURE'')

KF3 = CummKF1-CummKF2

KF4= Sfift KF3 by 1 bucket

KF5= If (Current bucket

then

if (KF3<0

then 0

else KF3

)

else

if((KF3=0 and KF4 not equal to 0)

then if (KF2-KF4<0

then 0

else KF2-KF4)

else KF2

)

)

KF5 will be your final result.

Hope this helps

-Rahul

[bindhu_konidala · Accepted solution]

Hi Rahul,

Thanks a lot for the detailed explanation.

I will try as suggested.

[rahulmohnot]

Hi Bindhu,

Kindly use IBP_RAGGR function in KF3, a new Key figure to sum the future values of the KF2 as shown below:

KF3@PL = IBP_RAGGR ("KF2@PL", ''SUM'', 0, N, ''CURRENTFUTURE'')

Here N= the length of the horizon. Example: N=24, if you want to sum 24 months in future.

Then

KF4 = KF1- KF3 will give you the required results.

Thanks

Rahul

[bindhu_konidala]

Hi Rahul,

Thanks for the quick reply.

In KF1 we have value in one period (22-Apr) only.

How it works with IBP_RAGGR or IBP_CAGGR ?

In the above example in case if we have KF2 (Apr-22) value as 100, then only the first period will become '0'.

We need to have something like KF2 front loading (from current to future) with KF1 value.

With rolling aggregation, the KF3 values will become 20,70,90,140,150 etc....

for the first 4 periods we can get correct values if we copy 100 to all the periods of KF1, but from fifth period onwards the values will not be correct.

Can you please elaborate.

Thank you.