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 !
Answers
-
Try using a Boolean with a summary of ANY instead of a number.
0 -
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
0 -
Hello @HugoVolpi
You can try using Average as the summary for this line item. This will show Americas as 1 instead of 3.
0 -
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
0 -
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.
0 -
@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.
0 -
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 !
0 -
@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
0
