Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bazzaonline
Creator
Creator

A count divided by an Average

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!

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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.

View solution in original post

5 Replies
anbu1984
Master III
Master III

Can you provide sample data and expected result?

JonnyPoole
Employee
Employee

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
Creator
Creator
Author

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
Employee
Employee

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.

View solution in original post

bazzaonline
Creator
Creator
Author

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