Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

sdemel
New Contributor

Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Hi,

 

Can I please get some help with the formula for this?

 

Thanks

8 REPLIES 8
swapnil_J
Frequent Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

1)The formula should check the total of all product SKU volumes over all time periods 

2) check if the value is not equal to zero. 

 

1) You need to use Timesum() function to sum Product SKU volumes over all time periods, Review timesum() function  :  https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/TIMESUM.html 

2) and the result you achieved using that function need to compare using a conditional operator to set boolean value true.

Review conditional operator:

https://help.anaplan.com/anapedia/Content/Calculation_Functions/Operators.html

 

 

sdemel
New Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Thanks very much.
patelpn3
Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Can you share your formula? thanks

anujtohani
New Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Hi, can anybody please help with this query?

What formula is to be used?

vishwajeetg
New Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

IF TIMESUM('DAT03 Historic Volumes'.Volumes) <> 0 THEN TRUE ELSE FALSE
jasonfortunato
Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Okay how did we get here?

 

Reviewing the TIMESUM syntax, the example is:

 

TIMESUM(TIMESUM line items.Number, 0, 4, SUM)

 

so my latest try was: TIMESUM(TIMESUM Has Data?, -1, 0, SUM)

 

which I thought would take data from each line which has a data in the Has Data? Boolean, from the last period (-1) to the current period (0) and sum it. 

 

This suggestion

IF TIMESUM('DAT03 Historic Volumes'.Volumes) <> 0 THEN TRUE ELSE FALSE

 

which works, doesn't seem to fit the syntax to me at all; how does it work?

Also why are we referencing DAT03? Isn't this a filter to be applied?

 

swapnil_J
Frequent Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Hi @jasonfortunato 

Both syntax are correct.

In first you are asking anaplan to sum up the values across the time period of the module and then to check where the sum is not equals to zero.

TIMESUM('DAT03 Historic Volumes'.Volumes) <> 0 

In below syntax you are using three more arguments(start period,end period and aggregation method) along with Number line items. In current scenario it is not required to have these 3 arguments.

TIMESUM(TIMESUM line items.Number, 0, 4, SUM)

 

jasonfortunato
Contributor

Re: Level 2 Sprint 1 - 1.5.7.2 Add Formula to SYS13 Line Item

Thanks for this... I also asked in class today and Anees disambiguated this a little bit:

 

The TIMESUM function ends after the parentheses:  TIMESUM('DAT03 Historic Volumes'.Volumes)

The boolean operators <> and value 0 are an IF statement separate from TIMESUM

The final portion is unnecessary as it is the default way Anaplan interprets <>0

 

So the most elegant solution would be:

TIMESUM('DAT03 Historic Volumes'.Volumes) <>0

 

Thanks to all!