Re: Count Total values - Qlik Community

Report Inappropriate Content · ‎2012-10-30

Hi all,

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????

Thanks

Gavin

Gysbert_Wassenaar · ‎2012-10-30

See attached qvw. I hope this helps.

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2012-11-01

Hi Gysbert,

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?

Thanks

Gavin

Report Inappropriate Content · ‎2012-11-01

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.

Thanks

Gavin

teempi · ‎2012-11-02

Hey Gavin,

Does something like Sum(Sale) / Count(DISTINCT TOTAL Name) do the trick?

-Teemu