A count divided by an Average

[bazzaonline]

Hello,  I have two datasets.

One is individual sales volumes for a group of customers in a month (so multiple sales per customers)

The second is the number of customers we had in a month.

I want to calculate the average number of sales per customer, is it possible as I have individual sales by day but customers as a monthly total??  Thank you in advance!

[anbu1984]

Can you provide sample data and expected result?

[JonnyPoole]

So if you sold to 2 customers   in a month and made 4 sales to one and 6 sales to another you want the average # of sales ... ie:   4+6  / 2  = 5 right ?

I would introduce a  key in your main table with the individual sales. in this example recno() is the unqiue identifier. If you do a count(distinct identifier) in a table it will give you the number of individual sales.

Load

     Sales

     Month,

     recno() as identifier

from <source>

so your expression will be:

count(distinct identifier)   -> # individual sales

/.

[#customers]   -> # of customers

You'll be able to analyze it by month.

[bazzaonline]

If I had customers Jan 10, Feb 15 and Mar 14

sales Jan 20, Feb 45 and Mar 28

I would like a graph Jan 2, Feb 3 and Mar 2.  Does that make sense?

My datasets show Jan sale 1, 2, 3 4 etc

Customers Jan 10

[JonnyPoole · Accepted solution]

I think you are saying that you want  the total sale $ (per month) /  total customers ( per month).  That is different to my example above.

In your chart (bar , line etc..)  add month as your dimension.

expression is:

sum(Sale) /  [#customers]

...where Sale is the field that reports an individual Sale$ for a month, and #customers is the field that reports the # customers for that month

if the explanation is not helping please post your QVW file.

[bazzaonline]

Thank you AGAIN, looks that that is another one you've resolved!  Brilliant, have an enjoyable evening,