4 Replies Latest reply: Nov 2, 2012 6:29 AM by Teemu Pitkänen

# Count Total values

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

See attached qvw. I hope this helps.

• ###### Re: Count Total values

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

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

Hey Gavin,

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

-Teemu