Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to figure out the percentage of how many times a given customer makes a purchase compared to other customers within in the same Facility and the same Category.
I have data that shows total counts of purchases for customers grouped by a concatenation of Facility and Category, but I am having a hard time showing the percent of purchases for a given customer compared to the other customers, for a given concatenation of Facility and Category. I thought some sort of aggregate may be the solution but I cannot figure it out.
I put the raw data into a table and created the below in Qlik:
Plant Category Concat | Facility | Customer | Category | Count of purchases per Facility/Category Concat |
1002ABRASIVE,BELT | 1002 | CRO | ABRASIVE,BELT | 17 |
1002ABRASIVE,BELT | 1002 | OEM | ABRASIVE,BELT | 3 |
1002ABRASIVE,BLOCK | 1002 | CRO | ABRASIVE,BLOCK | 1 |
1002ABRASIVE,BLOCK | 1002 | OEM | ABRASIVE,BLOCK | 1 |
1002ABRASIVE,BLOCK | 1002 | 218 | ABRASIVE,BLOCK | 1 |
1002ABRASIVE,BORE POLISH | 1002 | CRO | ABRASIVE,BORE POLISH | 1 |
1002ABRASIVE,COMPOUND | 1002 | CRO | ABRASIVE,COMPOUND | 1 |
1002ABRASIVE,COMPOUND | 1002 | OEM | ABRASIVE,COMPOUND | 1 |
1002ABRASIVE,DISC | 1002 | CRO | ABRASIVE,DISC | 8 |
1002ABRASIVE,DISC | 1002 | OEM | ABRASIVE,DISC | 1 |
Im looking for an additional column that shows the % of purchases per Customer in relation to the amount of purchases by other Customers for the same Facility / Category concatenation:
Plant Category Concat | Facility | Customer | Category | Count of purchases per Facility/Category Concat | Customer purchase % of Total |
1002ABRASIVE,BELT | 1002 | CRO | ABRASIVE,BELT | 17 | 85% |
1002ABRASIVE,BELT | 1002 | OEM | ABRASIVE,BELT | 3 | 15% |
1002ABRASIVE,BLOCK | 1002 | CRO | ABRASIVE,BLOCK | 1 | 33% |
1002ABRASIVE,BLOCK | 1002 | OEM | ABRASIVE,BLOCK | 1 | 33% |
1002ABRASIVE,BLOCK | 1002 | 218 | ABRASIVE,BLOCK | 1 | 33% |
1002ABRASIVE,BORE POLISH | 1002 | CRO | ABRASIVE,BORE POLISH | 1 | 100% |
1002ABRASIVE,COMPOUND | 1002 | CRO | ABRASIVE,COMPOUND | 1 | 50% |
1002ABRASIVE,COMPOUND | 1002 | OEM | ABRASIVE,COMPOUND | 1 | 50% |
1002ABRASIVE,DISC | 1002 | CRO | ABRASIVE,DISC | 8 | 89% |
1002ABRASIVE,DISC | 1002 | OEM | ABRASIVE,DISC | 1 | 11% |
Hi,
If you try to use total <> :
Sum([Count of purchases per Facility/Category Concat])
/ Sum(total <Facility, Category> [Count of purchases per Facility/Category Concat])
Aurélien