Solved: Using Frequency in a Chart Expression - Qlik Community

Report Inappropriate Content · ‎2009-09-09

This seems like such a simple thing yet i cant get my head around it.. I am trying to work out, in a chart the amount of times a customer has come back into a store per month.. I'm struggling with the expression.. If i use a Count of CustomerID all i get are the total amount of customers that came in per month (obviously).. I wish that there was an Aggregation called Frequency, would make things easier.. Please could someone suggest an easy way of doing this..

johnw · ‎2009-09-10

I think this:

avg(aggr(count(CustomerID),Month,CustomerID))

The aggr(...,Month,Customer) says that we want to group by Month and CustomerID. For each group, we want to count the number of occurrences of the customer that month, which is to say how many times the customer has visited the shop. The avg() then says take the average of all of those. Normally, that would simply be the average monthly frequency, not the average frequency for each month. But since month is your dimension, I THINK it should give you the average frequency per month. If it doesn't work, let me know. I suspect the answer is that or some simple variation on the theme.

View solution in original post

Report Inappropriate Content · ‎2009-09-09

Making the Month and customerID as two dimensions, and count(Distinct CustomerID) as expression, will be enough i think.

Report Inappropriate Content · ‎2009-09-09

Ok thanks i see what you saying but i want show this in a Bar chart, that doesn't work?? I think that would probably work in a Pivot Table or a Straight Table..

Report Inappropriate Content · ‎2009-09-09

I just tried it on a bar chart. It came like this. Not sure, whether this will server your purpose.

Report Inappropriate Content · ‎2009-09-09

No, more like this, so its more towards the average frequency per customer...

Report Inappropriate Content · ‎2009-09-09

But this was a sample chart sent to me which have to create in Qlikview..

Report Inappropriate Content · ‎2009-09-09

The graph you have shown displays only one cutomer's details it seems. But anyhow you doesn't want to create separate graphs for each customer, right? In that case the only thing you need to change in my graph is to change the order of dimensions. ie, Month should be first and then Customer. And then, on selecting a particular cutomer from a multibox, will give you graph similar to one you have shown. Correct me if i'm wrong.

Report Inappropriate Content · ‎2009-09-09

No its not for one customer, this is an average occurance for all the customers so for example in June the average amount of occurances per customer was just over 2.. No, just want to create one simple chart showing exactly as above.. I'll try your way non the less though...Thanks

Report Inappropriate Content · ‎2009-09-09

Ok that didn't work, i don't even know how you got your values..

Report Inappropriate Content · ‎2009-09-09

oops..actually the expression which i have written is count(Distinct BookingNo), because our application is for a travel company. So in your case, the expression should be the count of the 'salesid' or whatever by which you identify the customer visit. Sorry for mentioning it wrongly.