Contributor

How to add cell values for previous 8 months data only

I have an line item 'Amount' with data values. second line item(months applicable) which is boolean checked for only 8 previous month of current month. I want to add the values of these Boolean checked months only to be summed.

 

Can I please get some guidance here please?

 

Regards

Asha

3 REPLIES 3
Certified Master Anaplanner

Re: How to add cell values for previous 8 months data only

A few questions:

  1. Can you include some screenshots or examples with data so it's easier to see what you are trying to do?
  2. Do you want a sum of prior 8 months at just a single point in time or do you want to calculate that for every month (i.e. January 19 is the sum of prior 8 months data, February 19 is the sum of a different 8 months, etc...)
  3. Does your final target need to be dimensioned by time?

 

A few options come to mind, depending on the above questions/answers:

  1. Use TIMESUM - you wouldn't need a boolean line item with the last 8 months checked, but instead would specify that you want to add up the prior 8 months and it would do that calculation for each month in your target line item.
  2. Add up multiple LAG formulas - similar to above, but longer formula. The LAG formula is a bit easier to use than TIMESUM. 
  3. Set up a new staging line item that is dimensioned by the same thing as your original amount. In this staging line item, use an IF/THEN statement to say IF Boolean checked then Amount else 0. Then your final line item would be dimensioned by everything that the original amount line item is except for time. If you set your final line item equal to the staging line item, it will add up across time.

 

Happy to share more detail on the above, depending on what exactly you are trying to do.

Occasional Contributor

Re: How to add cell values for previous 8 months data only

Please try the following:

 

1. Create another line item (call it "Selected Amount"), and the formula is:

IF 'months applicable' THEN 'Amount' else 0

 

2. Assuming that you want to consider only the last 8 months EXCLUDING the current month, the formula of your line item is:

MOVINGSUM('Selected Amount', -8, -1)

 

If you want the last 8 months INCLUDING the current month, the formula is:

MOVINGSUM('Selected Amount', -7, 0)

 

I hope it helps.

Highlighted
Super Contributor

Re: How to add cell values for previous 8 months data only

few more best practices to compliment what @davy_widianto  said:

 

1. EARLY EXIT: IF not 'months applicable' THEN 0 else 'Amount' 

2. You could do Selected Amount[select:time.all periods] in your target module

 

Thanks

Arun