Comparing partial SUM values of lines of a list.

Hi, Anaplaners

From the first table in the attached file, I would like to compare (calculate changes in values in %) first year vs second year and second year vs thrid year. The former compares sum of 2012 - 2015 (2016 should not include as 2016 for the second year is always zero) and the latter compares 2012 - 2014. Is this technically possible ?

Tri_41066.xlsx

Accepted answers

Pooja

Hi @gregoryTan

The partial sums cannot be calculated directly but can be done instead.

Here is the work around below.

Hope this works for your stated problem.

Step1:Create a property called previous year in Year category List as per Screenshot below.

Step2:

In Calculation module create Contract Summary and Contract Summary 1 line items and put below attached formulae respectively.

Step 3: To calculate Change(%) use below formula then you will get desired result.

Thanks

Pooja

All comments

Pooja

Hi @gregoryTan

The partial sums cannot be calculated directly but can be done instead.

Here is the work around below.

Hope this works for your stated problem.

Step1:Create a property called previous year in Year category List as per Screenshot below.

Step2:

In Calculation module create Contract Summary and Contract Summary 1 line items and put below attached formulae respectively.

Step 3: To calculate Change(%) use below formula then you will get desired result.

Thanks

Pooja

Jaakko

Hi,

Do you use the native time dimension or custom year list? If you use the native time dimension this is simpler but one solution for custom year list also came into my mind. I tried to avoid SELECT-statements and hard coded formulas and wanted to keep in mind that there might be more than three year categories.

Option A) Anaplan timeline

1) Create a module for year categories to define the first and last year for that category. Also define previous year categories.

A Year Categories.jpg

2) Input contract values to years

A Contract Values.jpg

3) Add a second line item and a formula to lookup the previous year category value

A Previous Contract Values.jpg

4) Create a formula using TIMESUM for comparison (1st year would result in -100%, it is ignored)

A Comparison.jpg

Custom year-list

1) Define previous year category for each year category

B Year Categories.jpg

2) Map custom years to year categories. Here it is done manually but you could probably have a formula for this.

B Years vs Year Categories.jpg

3) Input contract values and create a second line item with previous year category lookup in the same way than in option A but with the difference that the previous value will be looked up only for years mapped in the step 2.

B Contract Values.jpg

4) Create the comparison. Again the 1st year must be ignored.

B Comparison.jpg

gregoryTan

Hi, Pooja

Thanks! I got exactly what I wanted!

gregoryTan

Thank you, Jaako.

In this case, I had to use custome time dimension instaed of that of Anaplan.

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385