SAC PLANNIG - Calculate YEAR FROM CALMONTH (DYNAMICALLY)

[davisg_81]

Hello everyone,

in my model there is only the CalMonth dimension.

I need to calculate dynamically the total for one year and divide it by the following year (see the example below).

How can I do it dynamically with a formula?

Thank you in advance.

[N1kh1l]

davisg_81

Your example image is not visible. Could you please attach it again.

Nikhil

[N1kh1l]

davisg_81

Thanks for the example. Below code would do it for you. Adjust scope for other dimensions accordingly. In my Data Action version is a parameter. I have done this for Quantity member of Account and written the results to AVRG_YEARLY_PRICE of Account

MEMBERSET [d/Date] = "202201" TO "202312" //Adjust accordingly, Can also add parameters
VARIABLEMEMBER #TOTAL_QTY_YEAR OF [d/Account]

DATA([d/Account] = #TOTAL_QTY_YEAR, [d/Date] = LAST()) = RESULTLOOKUP([d/Account] = "Quantity") // Adjust your Account member

DATA([d/Account] = "AVRG_YEARLY_PRICE") = RESULTLOOKUP([d/Account] = #TOTAL_QTY_YEAR, [d/Date] = PREYEARLAST([d/Date])) / RESULTLOOKUP([d/Account] = #TOTAL_QTY_YEAR, [d/Date] = LAST([d/Date]))

Output after the DA run.

Please accept/upvote if this helps.

Regards

Nikhil

[davisg_81]

Hi Nikhil,

thank you for your answer.

I followed your advice but I have the following error that I can not understand. The validation is right, but when I save the data action gives me the error in red

immagine-2022-08-02-154858.png

Thank you

Davis

[N1kh1l]

davisg_81

The syntax looks ok. Try removing the spaces in the DATA() statements. Write the DATA() statement in one line and then select the whole code and click format. Also easier if you can store the two divisions in separate variable and then finally divide them instead of doing the whole in one single line. Also your are running this for 2 months of 2023, Did you run this for 2022 before else how will you get total of 2022 ?

Regards

Nikhil

[davisg_81]

@nikhil_1486 This is the example:

Thank you in advance.