Compare values within a customer group / pivot table columns
I tried searching for a similar issue but could not find a solution.
I need to compare customer discounts for a subset of accounts and if they are not the same or some are missing flag it as an issue.
Ideally the output would be displayed in a pivot table with product code as the dimension and customer account number as the columns.
Example data attached.
A master customer has 5(number varies) different accounts across the group. Each account will have various discounts, some could have none at all. Once the master customer is selected, the output would compare the discounts across these 5 accounts for each product and if:
1. They are not the same
2. Some are missing
Create a flag to filter them on and all the issues be visible in a pivot table.
this is the data for 1st master, except for prod1, i changed prod 1 and 2 so there will at least be one row that will pass, and 1 row that will not have a missing discount but will have different discounts:
this is how it looks like after backfilling it with 0 discounts:
and this is your result:
only 1 row has uniform discounts and the rest have either non-uniform or missing discounts
Then As you want to flag the products having the issue, You do not need a Pivot table. Having a straight table for Products with Product as Dimension and following Expression should give you the desired result:
If(Count(Discount)=Count(Distinct Total Customer) and Count(Distinct Discount)=1,'OK','Issue')
I generally prefer doing this flag generating in the Script, to keep the expressions simple in the frontend. Using count Distinct is bad for performance if your dataset is large. So you could use the method suggested by @edwin and add Alert generation as the last step for each product.