Discussion Board for collaboration related to Creating Analytics for QlikView.
I'm struggling with a count total all number of distinct instances of a field to use as a divider in an expression.
I have a table that looks like this:
Customer Location Type Product Name Sale
1 UK Sale A Bob 100
1 UK Sale B Bob 50
1 UK Sale C Bob 50
2 UK Sale B Bob 50
2 UK Sale C Bob 50
3 Germany Sale A Fred 100
3 Germany Sale B Fred 50
3 Germany Sale C Fred 50
4 Germany Sale B Fred 50
4 Germany Sale C Fred 50
What I want is work out the average Sale value for each Location, Type, Product and Name based on a total count of Customer.
Basically the averages should come back to 50, the issue is product A, which is not always sold to all customers, but I want to look at the average by name, hence the average is 100/2, not 100/1.
I can't get this to reconcile even after trying aggr expressions, this does work, but for only one product line (A) but doesn't populate the others product lines in the table.
Any ideas out there????
See attached qvw. I hope this helps.
Thanks for the above, unfortunately it doesn't give the result I want which would look like the below:
Location Type Product Name Sale Avg
UK Sale A Bob 50
UK Sale B Bob 50
UK Sale C Bob 50
Germany Sale A Fred 50
Germany Sale B Fred 50
Germany Sale C Fred 50
The hardest calculation is for the total of the unique customers (in both cases 2) to divide the total sales by.
Any further thoughts?
Sorry further to the above, I know I can solve this by loading a table and grouping the count of the distinct customers into another table and using this as a dividing field, but I was hoping to use an expression instead.
Does something like Sum(Sale) / Count(DISTINCT TOTAL Name) do the trick?