Obtaining accurate summations in a Many-to-One Relationship
I am having trouble grouping lines correctly so that I can get accurate totals. The basic premise is as follows:
We are trying to transition away from buying item A and instead encouraging buying item X. However, the conversion is not a 1:1 relationship; there are some instances where item A -> X and B -> X and C to X. There are multiple pre-conversion (A,B,C, etc.) and post-conversion (X,Y,Z, etc.) items to deal from. Sometimes there is a 1:1 relationship, but many times there is this many:1 relationship as shown.
I would like to get the total quantity purchased of every item, i.e., both pre-conversion and post-conversion totals. However, the many:1 relationship causes a repetitive value problem:
Facility Pre-Convert Post-Convert
F1 A X
F1 B X
F1 C X
If, for example, X = 25, then the total amount of X purchased here would be 75 instead of the correct 25.
The Question: How do I group the X's into a single value per facility while also keeping the values of A, B, and C?
Some considerations:
1. The actual table is pivoted so that pre-convert and post-convert are separate lines. Trying to join them onto a single table to get a single line per conversion creates a many:1 join nightmare. On that note: if there is a way to solve this question using SQL or Python in a way that doesn't result in the same problem in a Discover (i.e., after that scripting is processed on the model flow), I am all ears.
2. I played around with grouping and list quick calculations. I don't think I'm using them correctly, and based on what I do know, I don't think it will help.
3. The best way I can think of would be to get the average of X per facility and use that as the value to sum across facilities. However, I don't know how I can group the aggregation by facility. I feel like that is the answer though.
Thank you,
JAF
3 replies
-
Also, just thinking out loud, perhaps there is a way in Python to take the value of (top 1 of X) per facility and set the rest of X per the facility to zero. There may be an issue with number of lines depending on how the dimensions are grouped in discover, but again, just an idea.
-
Hi
Trying to make sense of this:
Facility 1 used to purchase Items A, B and C, and is now purchasing X in their stead?
You want to show both the original purchase (A,B and C) as well the replacing item (X)?
Your data is actually:
F1 A 500F1 B 300
F1 C 200
F1 X 1000
?
If I understand correctly, could it also be that you have purchases of X that were not converted, but an actual purchase? Something like this:
F1 A 2022 500
F1 B 2022 300
F1 C 2022 200
F1 X 2022 1000
F1 X 2023 700
?
And you also have a table like this:
A X
B X
C X
?