How to add cell values for previous 8 months data only

AshaPatil
AshaPatil
in Modeling

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

Tagged:

Answers

  • bdeaton
    bdeaton

    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.

  • davy_widianto
    davy_widianto

    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.

  • ArunManickam
    ArunManickam

    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

Categories