How to avoid counting multiple times when summarizing

Hello everyone !

I came to ask a question here with a case i'm sttrugling with, i hope i will be clear enough with my explanation, don't hesistate to tell me if it is not clear.

To contextualize a bit : i'm working basically with two hierarchical lists that goes from :

GP0 Zone, GP1 Region, GP2 Country, GP3 Store
P1 Subvision, …….., P6 Reference-Color

I have a Line Item where i retrieve an information that says : the number of P6 Items that were ticked by GP1 Region. For example here, i have a P6 Item that has been ticked by one GP3 Store in the US , hence the value 1 for US RWH (at GP1 Region level), same goes for MEXICO RWH

Now i need to retrieve the info at the GP0 Zone Level (AMERICAS, in my example), that goes like this :

The issue i'm having here is that when i display datas at the GP0 level , my Line Item will count 2 meanwhile i should get 1 because both US RWH and MEXICO RWH ticked the same P6 Reference (as shown in the screenshot above).
I don't know if anyone here has been through that kind of case but i'll be thankful if anyone has a tips !

Thank you very much !

Accepted answers

All comments

rob_marshall

@HugoVolpi

Try using a Boolean with a summary of ANY instead of a number.

HugoVolpi

Thank you Rob for your reply !

About ANY, you suggest using that rule at the root of the calculation ? (where i'm counting the tick per RWH right ?) I was wondering, does the ANY take care of the redundancy in that case ?

Here for example i should get 1 for AMERICAS instead of 3 because all the childs ticked the same P6 Items

Indeed applying any will remove the counting that occurs here, but then how do you aggregate that in a count ? (because it's a quantity that i need to display like this screenshot)

Thank you very much for your answer !!

Best

Anmol14

Hello @HugoVolpi

You can try using Average as the summary for this line item. This will show Americas as 1 instead of 3.

HugoVolpi

Hello @Anmol14 , thank you for your feedback,

Indeed it works when all of them are being ticked, but if we take for example one of the previous season, here 22FW where only US RWH is being ticked , then it won't display one since it's making an average.

But thank you very much for your answer, it was a good idea

Anmol14

Hello @HugoVolpi,

Thank you for pointing out the limitation of Avg in your case. I inspected further from this current screenshot, that since the value can never be more than one, you can also try using MAX as a summary because this will give 1 at a higher level even if it is 0 for the other list items at the lower hierarchy level.

Best,

Anmol.

TristanS

@HugoVolpi not quite sure if this is what you need but you can try Ratio for your summary. It will yield the following result (refer to lineitem a). But you will need to create an additional lineitem as basis for the Ratio. Screenshot for both result and formulas below. Personally, I would have kept the calculation as a separate lineitem/module for that but you probably have a use case you need it in this format.

HugoVolpi

Hi @TristanS

First of all, thank you very much for your detailed answer. The exepected result is indeed what the the Line Item b is doing in your screenshot.

I just have one question regarding the line item B, how does he connect with the line item a here ? in order to get its value ? ( also i assume C1 is the parent of C2 right ?)

Thank you very much !

TristanS

@HugoVolpi It connects with line item a via using Ratio as the Summary method for line item a. This is in the last 2 screenshots in my original post to this thread highlighted in yellow

Quick Links

Can you help?

Unanswered questions

A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…
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