Hi everyone,
Hope this is the right place for this. Let me know if i have messed up.
I am trying to show sales opportunity in a dashboard i am creating. This is done by showing which customers haven't bought a product. The nature of the data is such that a row is only added when a sale is made or a return is processed.
Example data
| Customer | Product | QtyShip |
|---|
Customer A | Potatoes | -10 |
Customer A | Potatoes | 20 |
| Customer A | Peas | 15 |
| Customer B | Potatoes | 2 |
| Customer B | Carrots | 20 |
So my problem comes up when I need to work out what products the customers have NOT bought.
I need to create a chart that would show something like this.
| Customer Opportunities |
| Customer | Product |
|---|
| Customer A | Carrots |
| Customer B | Peas |
Now I could simply modify the data in the script so that every customer has every product with a Cross Join and 0 Qtyship. However as I have many thousands of customers and many thousands of products this significantly increases the size of my data model.
I want to know if there is a way I can do this as an expression in a chart.
Simeon
P.S
This is further complicated as in my actual data set there are sub versions of products and i want to give the user the ability to show and hide columns to make it easier on them. this would change the number or products or sub products that have null rows associated
