Formula problem - adding values in the same line item

Jsdeloria21
Jsdeloria21
in Modeling

Hi Guys,

 

I just want to ask help on the formula. I was able to create a formula and got the values but the succeeding time does not show no values.

 

This is what it looks like in excel

Jsdeloria21_1-1580370791159.png

 

I also included the formula on the sheet for col K, L and M. Row 7 will just add values.

 

I got the first two values correctly. See below

 

Jsdeloria21_2-1580372201332.png

Row 6 line item is the one that needs the formula for the SUM in excel.

 

Appreciate the help

 

Regards,

 

 

 

Tagged:

Best Answers

  • Misbah
    Misbah
    Answer ✓

    @Jsdeloria21 

     

    If the values need to be incremental then your formula should be

     

    ELSE (Previous(Row 6) +1)  & not ROW 5

     

    Thanks,

    Misbah

     

  • naushad786
    naushad786
    Answer ✓

    Hi @Jsdeloria21 ,

     

    As @Misbah told to you that In the ELSE Condition you need to put ROW 6 Instead of ROW 5. Please find the below screenshot.

     

     

    naushad786_1-1580376321176.png

     

     

    Thanks

  • Jsdeloria21
    Jsdeloria21
    Answer ✓

    Hi Misbah,

     

    Appreciate the help on the issue. I just want to know how can I continue the numbers 1, 2, 3, and so on. Apparently the clients requirement is continuous counting until the end of the time range. 

     

    Regards,

  • AWhitworth
    AWhitworth
    Answer ✓

    There are a whole host of Anaplan formulas that work with time. As you are trying to sum over a range of months in your excel file I would recommend using the MOVINGSUM formula.

     

    Here is a link to the Anaplan Time and Date Functions Anapedia page where you can get more information: 

    https://help.anaplan.com/anapedia/Content/Calculation_Functions/CF_Time_Date_Functions.htm 

  • AlejandroGomez
    AlejandroGomez
    Answer ✓

    Hi @Jsdeloria21 

     

    To judge from the comments and the pic attached in your second post, I would say that you should use CUMULATE

    AlejandroGomez_0-1580465802431.png

    CUMULATE will accumulate the "1" along the time period. As you can see, TIMESUM will retrieve the total of the whole time-dimension (unless you specify the beginning and ending of the time range you want to use).

     

    I hope this helps. 

     

    Alex.

    Olivehorse

     

     

  • rob_marshall
    rob_marshall
    Answer ✓

    @AlejandroGomez 

     

    For performance reasons, previous() is faster than Cummulate() in a module using time as previous looks at just the previous member, but cumulate() hits all members over and over.

     

    Rob

  • AlejandroGomez
    AlejandroGomez
    Answer ✓

    Hi again @Jsdeloria21 ,

     

    In order to meet your need and improve performance as per @rob_marshall  suggestion, you can use the formula shown below:

    AlejandroGomez_0-1580478314895.png

     

    As you can see, the output is exactly the same as CUMULATE (which is what I understood you need), but it only uses PREVIOUS. 

     

    Thanks Rob for your input.

     

    Let us know if it works


    Alex.

  • sandeep_bk
    sandeep_bk
    Answer ✓

    @Jsdeloria21,

     

     I believe this might help you with the alternate workaround.

     

    The problem here is with the else part. i.e formula says if previous('Row 5')<1 then 'Row 5' else previous('Row 5')+1.

     

    Else should be greater than 1 according to the formula and hence it would update only July 18 as 2. ( Rest of the time series won't fall under else part)

     

    You can copy the Row 5 value to some dummy line item with the yearvalue() or some formula and apply the logic.

     

    sandeep_bk_0-1580917597997.png

    Again I am not sure whether it helps in your case.

     

    Thanks,

    Sandeep

Categories