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: 
dvasseur
Partner - Creator III
Partner - Creator III

calculating how many customers have purchased this product at least twice

Hi,

For some market analysis I'd like to calculate, per product, how many customers have purchased this product at least twice.

My formula is working if there is only one product selected but not otherwise, see attached sample.

I have hundreds millions of rows so an optimised expression is required 😉

Regards,

David

3 Replies
swuehl
MVP
MVP

You can try creating an ID for each combination of customer and product (see the example in the script, you can performance optimize this, just want to show the principle).

See attached.

dvasseur
Partner - Creator III
Partner - Creator III
Author

It would be hard to create such a field (one sales line could be linked to 2 customers...), but anyway, how could that help me ? In my Aggr() function ? (the QVW you have attached is not reloaded and there is no new expression).

swuehl
MVP
MVP

Ok, sorry for that.

Reloaded and modified expression.

This does not account for multiple customers per sales line, my method would imply duplicating sales lines then (or a link table).

In general, you'll need to decide what you want to optimize. Optimizing run time performance for a certain analysis often requires to create a data model optimized for that need, but this model may not fit all analytical questions you want to answer.

Set analysis is evaluated once per chart, not per dimension line so it won't consider your current dimension value.

I think you know that, but if not, here is a document that discusses this in more detail (as well as a possible workaround, which might be applicable to your issue if you have only some distinct products (not millions)).