topic Re: A count divided by an Average in QlikView

A count divided by an Average

bazzaonline — Mon, 22 Sep 2014 12:46:13 GMT

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!

Re: A count divided by an Average

anbu1984 — Mon, 22 Sep 2014 12:52:01 GMT

Can you provide sample data and expected result?

Re: A count divided by an Average

JonnyPoole — Mon, 22 Sep 2014 12:54:41 GMT

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.

Re: A count divided by an Average

bazzaonline — Mon, 22 Sep 2014 13:00:43 GMT

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

Re: A count divided by an Average

JonnyPoole — Mon, 22 Sep 2014 13:19:12 GMT

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.

Re: A count divided by an Average

bazzaonline — Mon, 22 Sep 2014 14:14:58 GMT

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