topic Using frequency to develop a counter in QlikView

Using frequency to develop a counter

Tue, 02 Nov 2010 14:45:55 GMT

Hello,

I am just starting into using qlikview so please excuse any "dumb questions". I am very comfortable with displaying the data etc. but calculations are causing me issues. Basically I am trying to develop a report that will show the % of business that comes from current customers vs. new customers. I can display all the info I want but I can't seem to figure out how to get this percentage to work.

The data I have is a table of order information. What I am doing is looking at their customer number and if the frequency is > 1 then they are a current customer, if it is <1 they are a new customer. eventually i want to build in timeframe around fiscal year but for now I can't figure out how to do the calculation to get the percentages. Then the display will either go to a graph or a data box .. doesnt' really matter.

Thanks so much for the help in advance.

Using frequency to develop a counter

pover — Tue, 02 Nov 2010 14:59:35 GMT

I might not be understanding something, but would the following work?

count(if(Customer<1,Customer))

count(if(Customer>1,Customer))

Regards.

Using frequency to develop a counter

Tue, 02 Nov 2010 15:25:01 GMT

hmm I might be doing something wrong but that didn't exactly work.. So maybe more info would help.

I have this type of data

customer # , name, address, etc..

Since some of the names are entered differently I am relying on the customer # to be the unique identifier. So I created a table that shows all the numbers and their frequency.

my thoughts were like this

if (customer# frequency > 1 ) current customer + 1

else new customer +1

then display:

(current customer / total customer)*100

(new customer / total customer ) * 100

does the count function take care of this or should I be using aggr or something else?

Using frequency to develop a counter

pover — Tue, 02 Nov 2010 15:51:53 GMT

The count(if) should work.

count(if(Customer_Frequency<1,CustomerNo))

count(CustomerNo)

If the frequency is calculated in a table this will problably work for the current month, but to do analysis for every month, you could use the customer first purchase date. You can play with a formula like this:

count(aggr(if(monthname(min(total Order_Date))=Month_Year,CustomerNo,null()),CustomerNo))

Month_Year is a field you create in your script using the monthname() function and you use in the dimension of the chart.

Regards.

Edit: A total modifier is also probably going to be necessary.

Using frequency to develop a counter

Tue, 02 Nov 2010 17:17:10 GMT

I think I realized the problem and it is all about my approach. I was thinking I could use the frequency that the list box automatically calculates, but I need to do that myself as part of the script.. correct?

Using frequency to develop a counter

pover — Tue, 02 Nov 2010 18:26:31 GMT

The frequency in the list box is basically a count of the values in that field, but you won't be able to use it to determine a new customer just like that. You can either use a first_date_purchased field or compare if the client had bought something in a past period compared to the current period. For the last option, you can use set analysis having the user select a certain month:

count({$<CustomerNo = E({$<Order_Date={"<=$(=date(max(Month_Year)))"}>})>} CustomerNo)

The E() exlcludes all customers with a Order Date before the Month Year selected by the user which will give you a count of the new customers of the period selected.

These functions aren't tested, but hopefully give you an idea in what direction to go. If you have a more specific question about syntax once you've started playing with your real data just post again.

Regards.

Using frequency to develop a counter

Tue, 02 Nov 2010 18:57:39 GMT

you have been a great help, thank you very much.

Before I get into working with dates, I simply want to look at my entire data set and return the % of customers that have made multiple orders vs. % of customers that have made 1 order (current vs. new).

I am able to display if they are a current or new customer in a list box with

count (if ([SAP order #] <= 1), newcustomer)

and the opposite for current.

What I can figure out how to do is to get the expression to just give me the total "count" from that if statement.

The idea being is I can show customer data if they are a new v/s current, but also show a graph or a number (percentage) of new vs. current. I understand the bottom half (divide by) of the formula count distinct sap order # , it is just top that I can't seem to understand

Using frequency to develop a counter

pover — Tue, 02 Nov 2010 19:15:17 GMT

I suppose there might be customers without any order so a count(distinct customer) might return percents that don't add up to 100% so it might be better to just sum the two count(if)'s like below in the expression of a chart:

count(distinct if([SAP order #]<=1,customer))

count(distinct if([SAP order #]>0,customer))

Note that the example that you put in your post shouldn't have a ) after <= 1, but two ) after newcustomer like the examples I've included.

Does that work, or am I missing something?

Using frequency to develop a counter

Tue, 02 Nov 2010 21:42:02 GMT

If i do count distinct [SAP order #] that works and returns a value,

but when I do this: count (distinct if ( [SAP Account #]<=1, [Bill To Name]))

I get no result back.

Using frequency to develop a counter

pover — Wed, 03 Nov 2010 20:44:23 GMT

The syntax looks fine. Does the field [SAP Account #] have values equal to or less than 1 that correspond to [Bill To Name]?

Regards.