topic Count Total values in QlikView

Count Total values

Tue, 30 Oct 2012 19:12:27 GMT

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

Re: Count Total values

Gysbert_Wassenaar — Tue, 30 Oct 2012 19:34:40 GMT

See attached qvw. I hope this helps.

Re: Count Total values

Thu, 01 Nov 2012 12:23:51 GMT

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

Re: Count Total values

Thu, 01 Nov 2012 12:31:15 GMT

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

Re: Count Total values

teempi — Fri, 02 Nov 2012 10:29:15 GMT

Hey Gavin,

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

-Teemu