Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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

Highlighted
Contributor
Contributor

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.

Highlighted
Creator III
Creator III

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

concatenation will done in order of date field

Highlighted
Contributor
Contributor

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!

Highlighted
Contributor
Contributor

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!