Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fluxfrog
Contributor II
Contributor II

Count purchases with previous demos

Hi!

I have an app that monitor software sales. Customers can buy "full" products or get a "demo". I would like to visualise how useful the demos really are by comparing the number of purchases that were preceded by a demo to the direct purchases.

My attached table shows some example data, and also the numbers I would like to get out of this. Can you help me to figure out the best way of achieving this?

Thank you very much!

1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III

you can do it in pivot chart, under columns add 

create a dimension

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Demo*Full'),'fullafterdemo',

 

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Full'),'Fullonly',

 

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Demo'),'demoonly')))

 

in expression(values) create count( CustomerID)

View solution in original post

5 Replies
asinha1991
Creator III
Creator III

you can do it in pivot chart, under columns add 

create a dimension

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Demo*Full'),'fullafterdemo',

 

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Full'),'Fullonly',

 

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Demo'),'demoonly')))

 

in expression(values) create count( CustomerID)

fluxfrog
Contributor II
Contributor II
Author

Wow, what a dirty trick (in a good way). But, it does require that the data is sorted according to date, right? This doesn't explicitly compare the dates but rather look for concatenated strings with "Demo" followed by "Full" if I understand the code correctly.

asinha1991
Creator III
Creator III

in concat you can add an ordering field, I did add date

concatenation will done in order of date field

fluxfrog
Contributor II
Contributor II
Author

Clever! I didn't know that. And yes, it works! But, unfortunately, it is a bit too slow with millions of lines in that table. However, I think I could get what I want in the load script using GROUP BY and the same strategy, to create some sort of helper table with this information pre-calculated. I'll give it a try!

Thank you very much!

fluxfrog
Contributor II
Contributor II
Author

For the sake of completeness. Using GROUP BY and concat , and then wildmatch to create a new table worked fine. Adds a few seconds during load, and is then instant in the app more or less. Great!