Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm looking for a way to calculate and chart the average sales of different products across all customers. By default Qlik Sense seems to calculate the average sales per customer who purchased that item.
What I get: Total sales of apples / Customers who purchased apples
What I want: Total sales of apples / Total customers
I'm currently using this expression: Sum(SALES)/Count(distinct CUSTOMER_ID)
What would be the correct expression?
Below is some sample data I created to explain the problem. The table shown in the screenshot is the complete dataset. In this example, a single customer have purchased a lime, so the average sales of lime is 1 in the chart. I would like this to be 0,1 (sales divided by all customers).
maybe with a TOTAL
Count(distinct TOTAL CUSTOMER_ID)
Thank you, that did the trick!
In my actual data, I have a period dimension as well, so I had to add "<PERIOD>" to make it not ignore the period dimension:
Sum(SALES)/Count(distinct TOTAL <PERIOD> CUSTOMER_ID)