topic Using Frequency in a Chart Expression in QlikView

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 16:21:24 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 16:40:53 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 17:04:05 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 17:24:39 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 17:44:46 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 17:59:27 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 18:49:00 GMT

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.

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 18:52:29 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 19:32:56 GMT

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

Using Frequency in a Chart Expression

Wed, 09 Sep 2009 19:44:32 GMT

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.

Using Frequency in a Chart Expression

johnw — Thu, 10 Sep 2009 05:57:19 GMT

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.

Using Frequency in a Chart Expression

Thu, 10 Sep 2009 15:01:59 GMT

Thanks so much John, worked perfectly, exactly what i was looking for...

Re: Using Frequency in a Chart Expression

Wed, 13 Aug 2014 22:49:34 GMT

John,

This suggestion works nicely, thank you, as you can see in the attached Frequency of orders for Data Center.qvw.

There is one issue, however, not relating to the script.

The Customer, Data Center, has 3 orders. One order, DDV9828, skews the average. For each of its (4) Products ordered, there is a corresponding canceled Product order. As a result, the script averages 8 orders, when we might want to say there is no order.

Do you have any suggestions for addressing this issue?

Oliver