How to get the FY19 and the FY20 values in the same Line Item.

riyazpasha
Contributor

How to get the FY19 and the FY20 values in the same Line Item.

Hi need help on the below query.

I have three line items.

A, B, C where in A and B are the inputs and the C is based on the Formula.

IF (PREVIOUS(C) + A + B) < 0 THEN -(PREVIOUS(C) + A + B) * 0.0386 ELSE (PREVIOUS(C) + A + B) * 0.0386

 

The Function previous gives the value of the previous month. But I want the value of FY19 in the place of PREVIOUS(C) value. The FY19 value which I want in place of PREVIOUS(C) is fixed for the all the months of FY20 and the value of Dec20 is fixed for all the months of the FY21.

Note : The values of FY19 and FY20 are not the sum of the full year.

 

I'm not getting the idea about how to get the value of FY19 in the place of previous(c) - in the above formula.

I have attached the excel screenshot and Anaplan screenshot. 

 

 

Looking forward for the help.

Thanks in advance.

4 REPLIES 4
rob_marshall
Moderator

Re: How to get the FY19 and the FY20 values in the same Line Item.

@riyazpasha 

 

Would YearValue() not work for you?

 

IF PREVIOUS(YEARVALUE(C)) + A + B < 0 THEN -(PREVIOUS(YEARVALUE(C)) + A + B) * 0.0386 ELSE (PREVIOUS(YEARVALUE(C)) + A + B) * 0.0386

 

Rob

riyazpasha
Contributor

Re: How to get the FY19 and the FY20 values in the same Line Item.

Hi @rob_marshall 

Thanks for Quick reply.

 

This will not work as I will get the circular reference error. The screenshot that I have attached where in I'm adding this formula in the Line Item C.

 

Anaplan.PNG

jasonblinn
Master Anaplanner/Community Boss

Re: How to get the FY19 and the FY20 values in the same Line Item.

@riyazpasha If I understand your issue then you are trying to pull the December number into the next year, aka the closing balance is the summary method? 

 

I have solved this by doing the following:

jasonblinn_2-1607067558601.png

 

I have added a Line item Called "Period to Lag" Which is just calling out the month of the year using MONTH(ITEM(TIME)) as the formula (This would probably be better suited in a time settings System module than here)

 

I added another line item Called "Yearly Number" that allows the .0386 or the .02 to be inputs by year since it appears they change by year.

 

A and B are still inputs per your request, and C I have simplified:

In your original formula, it seems like you want it to be a positive number even if it calculates as a negative, so instead of using the IF statement, I have just used the ABS() formula to show the Absolute Value.

Secondly, I am using the LAG() Formula to Move the December number a certain number of periods. We can use the "Strict" Mode here to prevent the formula from having a possibility of circularity.

 

Here is what my final formula looks like: ABS(A + B + LAG(C, Period to Lag, 0, STRICT)) * YEARVALUE(Yearly Number)

 

jasonblinn_3-1607067670930.png

 

 

My Yearly Number line item simply looks like this and is an Input: 

jasonblinn_4-1607067709433.png

 

Hope this helps!
Jason

 

 

 

riyazpasha
Contributor

Re: How to get the FY19 and the FY20 values in the same Line Item.

Hi @jasonblinn 

 

Thank you so much it worked for me. Thanks a lot.