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

    Count Total values

    Gavin Mowling

      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

          See attached qvw. I hope this helps.

          • Re: Count Total values
            Gavin Mowling

            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