INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

pdekas
Contributor

INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

Hi guys, 

pdekas_0-1627481741475.png

 

i need your help, im struggling with this, i will try to be as clear as possible .

  • i have this dashboard, column 2 is what i have now and  column 3 i what im trying to achieve (i added the column 3 via excel).

i would like to know if its possible to achieve the following setting in anaplan.

the column 2 will be used as a follow up column to tell the end user how much they still need to dispatch. The total highlighted in black is the starting value that is allocated from january to december. The column 2 is supposed to display the remaining amount to be dispcatched for the period, the formula would be starting in february since january will be total for all month ( black highlighted cell) minus january's total :

Column 2 formula should go like this : target value at n  = value from the previous cell ( to be allocated value at n-1) minus the total value at n (column 1) . with this, we will end up at zero for the last period ( december )

  • plus : i will like to know how i can apply a conditionnal formating just in the last cell or in total cell at the bottom.

Thanks in advance

12 REPLIES 12
rob_marshall
Moderator

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

@pdekas 

 

Yes, if you are using Native Time, then you can use the previous() function which you will need.

rob_marshall
Moderator

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

@pdekas 

 

Something like this?

2021-07-28_12-18-42.png

pdekas
Contributor

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

HI,
yes something like this. could you please explain or show what do you mean by native time? i tried using the time module i have with this formula but it didnt work.

i have this error : 'LAG' function may not be used if the result line item does not have a timescale

rob_marshall
Moderator

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

@pdekas 

 

Yes, some people use a custom time list vs using the one that is native to Anaplan, which it looks like you are and I would question as why you aren't working with the "real" time list that is native to Anaplan.  If you use the "real" time list/dimension, then the above will work because you can you the Previous() and LAG() functions which you can not use with custom lists.

 

With the error message you received , you are not using the "normal" time list, but rather a custom one.

 

Hope this helps,

 

Rob

 

pdekas
Contributor

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

Ok, this may sound stupid but how to use the native time ? i tried by creating a new module with the automatic time as dimension but i still can't figure why i am getting the same error. could you please share a pic of the 'SYS Time Filter-Month'.FY Year blueprint?
rob_marshall
Moderator

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

@pdekas 

 

Not a stupid question, I should have detailed it out better and now I just realized we can make it more peformant with less calculations (amazing what a good night sleep will do for you).

 

Create a SYS Time module using Time with the following line items:

 

Line Item Format Formula Summary TimeScale
First Time Period Boolean LAG(1, 1, 0) = 0 None Month
Month Only Boolean TRUE None Month
FY Year Year item(time) none Month
FY Year Bool Boolean TRUE None Year
Month and FY Year Boolean Month Only or FY YEAR Bool Formula Month

 

 

2021-07-29_08-32-20.png

 

2021-07-29_08-32-45.png

 

 

Create a module for your calculation, I called mine Allocation.

 

Create a line item for the input, mine is Data to be Allocated and is a number format.

Create another line item called Final $ with the formula:

IF 'SYS Time Filter - Month'.First Time Period THEN Data to Allocation[LOOKUP: 'SYS Time Filter - Month'.FY Year] - Data to Allocation ELSE PREVIOUS(Final $) - Data to Allocation

 

Notice, this is different from the original one because now I am looking for the First Time Period in the SYS Time Filter module and just referencing it instead of redoing the calculation again.

 

 

2021-07-29_08-34-12.png

 

2021-07-29_08-34-20.png

 

Hope that helps,

 

Rob

 

pdekas
Contributor

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

Thanks a lot,
i followed all these steps but i got this error :

LOOKUP using DATE mapping SYS TIME.FY Year is not valid in this case because Total VAT included does not apply to Time
rob_marshall
Moderator

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

@pdekas 

 

Look at your formats and compare them to mine.  Or, put a picture of your blueprint when you respond so I can see what is going on.

pdekas
Contributor

Re: INCREMENTAL CALCULATION AND CONDITIONL FORMATTING

Hi, 

pics of the blueprint: 

pdekas_4-1627629406562.png

 

pdekas_3-1627629337566.png

 

pdekas_1-1627629116684.png

pdekas_2-1627629249342.png

 

and the formula im trying to apply to allocation :

IF SYS TIME.First Time Period THEN Total VAT included[LOOKUP:SYS TIME.FY Year] - Total VAT included ELSE PREVIOUS (allocation) - Total VAT included

 

Thanks a lot for your time 🙂