Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using frequency to develop a counter

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.

9 Replies
pover
Partner - Master
Partner - Master

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

count(if(Customer<1,Customer))

/

count(if(Customer>1,Customer))

Regards.

Not applicable
Author

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?

pover
Partner - Master
Partner - Master

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.

Not applicable
Author

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?

pover
Partner - Master
Partner - Master

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.

Not applicable
Author

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

pover
Partner - Master
Partner - Master

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?


					
				
			
			
				
			
			
			
			
			
			
			
		
Not applicable
Author

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.

pover
Partner - Master
Partner - Master

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

Regards.