Formula problem - adding values in the same line item

Jsdeloria21
Frequent Contributor

Formula problem - adding values in the same line item

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,

 

 

 

8 ACCEPTED SOLUTIONS

Accepted Solutions
Misbah
Moderator

Re: Formula problem - adding values in the same line item

@Jsdeloria21 

 

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

 

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

 

Thanks,

Misbah

 

View solution in original post

naushad786
Frequent Contributor

Re: Formula problem - adding values in the same line item

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

View solution in original post

Jsdeloria21
Frequent Contributor

,Re: Formula problem - adding values in the same line item

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,

View solution in original post

AWhitworth
Certified Master Anaplanner

Re: Formula problem - adding values in the same line item

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 

View solution in original post

AlejandroGomez
Master Anaplanner/Community Boss

Re: Formula problem - adding values in the same line item

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

 

 

View solution in original post

rob_marshall
Moderator

Re: Formula problem - adding values in the same line item

@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

View solution in original post

AlejandroGomez
Master Anaplanner/Community Boss

Re: Formula problem - adding values in the same line item

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.

View solution in original post

sandeep_bk
Regular Contributor

Re: Formula problem - adding values in the same line item

@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

 

 

 

 

 

View solution in original post

8 REPLIES 8
Misbah
Moderator

Re: Formula problem - adding values in the same line item

@Jsdeloria21 

 

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

 

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

 

Thanks,

Misbah

 

View solution in original post

Jsdeloria21
Frequent Contributor

,Re: Formula problem - adding values in the same line item

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,

View solution in original post

naushad786
Frequent Contributor

Re: Formula problem - adding values in the same line item

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

View solution in original post

AWhitworth
Certified Master Anaplanner

Re: Formula problem - adding values in the same line item

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 

View solution in original post

AlejandroGomez
Master Anaplanner/Community Boss

Re: Formula problem - adding values in the same line item

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

 

 

View solution in original post

rob_marshall
Moderator

Re: Formula problem - adding values in the same line item

@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

View solution in original post

AlejandroGomez
Master Anaplanner/Community Boss

Re: Formula problem - adding values in the same line item

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.

View solution in original post

sandeep_bk
Regular Contributor

Re: Formula problem - adding values in the same line item

@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

 

 

 

 

 

View solution in original post