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

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

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

Appreciate the help

Regards,

Accepted answers

Misbah

@Jsdeloria21

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

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

Thanks,

Misbah

naushad786

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.

Thanks

Jsdeloria21

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

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

Hi @Jsdeloria21

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

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

@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

Hi again @Jsdeloria21 ,

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

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.

SandeepB

@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.

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

Thanks,

Sandeep

All comments

Misbah

@Jsdeloria21

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

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

Thanks,

Misbah

naushad786

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.

Thanks

Jsdeloria21

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

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

Hi @Jsdeloria21

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

I hope this helps.

Alex.

Olivehorse

rob_marshall

@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

Hi again @Jsdeloria21 ,

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

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.

SandeepB

@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.

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

Thanks,

Sandeep

Quick Links

Can you help?

Unanswered questions

Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…