cancel
Showing results for 
Search instead for 
Did you mean: 

Subtract data at Different Category between 2 years

former_member590808
Participant
0 Kudos

Hi,

We have data at Category ESTIMATE[2018_19] and data at CATEGORY Budget[2019_20].

I have to calculate Variance in between them i.e. Data at Budget,2020.03-Data at Estimate,2019.03 etc. I will have to do this calculation for 5 months.

I tried writing a member formula but I am not aware how to fetch Time period at run time.

Should I write a logic script for this ? If I am writing a logic Script how should I do the subtraction between two data combination ? The below script gave me error.
Please help.
Regards,

Neha

I tried it in *REC((FACTOR=1,APL_TIME=%APL_TIME_SET%,CATEGORY=BUDGET)-(FACTOR=1,APL_TIME.TMVL(-12,%APL_TIME_SET%),CATEGORY=ESTIMATE))

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

The correct script will be:

//%APL_TIME_SET% = 2019.01,2019.02,...,2019.05
*XDIM_MEMBERSET CATEGORY=BUDGET,ESTIMATE

*FOR %T%=%APL_TIME_SET% //Calculate each time member
*XDIM_MEMBERSET APL_TIME=%T%,TMVL(-12,%T%)

*WHEN CATEGORY
*IS BUDGET
  *WHEN APL_TIME
  *IS %T%
    *REC(FACTOR=1,CATEGORY=BUD_VAR)
  *ENDWHEN
*IS ESTIMATE
  *WHEN APL_TIME
  *IS TMVL(-12,%T%)
    *REC(FACTOR=-1,CATEGORY=BUD_VAR,APL_TIME=%T%)
  *ENDWHEN
*ENDWHEN

*NEXT

You may also think about PREVYEARMONTH property in APL_TIME dimension to avoid FOR/NEXT...

former_member186338
Active Contributor

Answers (1)

Answers (1)

former_member186338
Active Contributor
0 Kudos

First:

*REC((FACTOR=1,APL_TIME=%APL_TIME_SET%,CATEGORY=BUDGET)-(FACTOR=1,APL_TIME.TMVL(-12,%APL_TIME_SET%),CATEGORY=ESTIMATE))

Absolutely unsupported syntax!

Second - please explain the logic once again:

"I have to calculate Variance in between them i.e. Data at Budget,2020.03-Data at Estimate,2019.03"

And where do you want to store the result?

former_member590808
Participant
0 Kudos

I have to Subtract data present at Category: Budget, Time:2020.03 with data present at Category: Estimate, Time:2019.03 and make the difference to appear at category BUD_VAR.