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: 
dana
Creator II
Creator II

Set Analysis with nested AGGR

Hi People,

I have a request to display returning customers as a dynamic KPI, meaning, for  the previous 6 month of a selected date. 

The user selects a product, and a returning customer would be a customer that bought one or more product after the first buying  date of the selected product. I'm struggling with a Set Analysis expression to  count the relevant  customers. 

I have managed to calculate the number of return dates:

Sum(aggr(Count({<Creation_Date = p({<Product_Group=, Creation_Date = {">$(=min(Creation_Date))"}>} )>}DISTINCT Creation_Date),Customer))

In order to count the relevant customers I added:

=Count({< Customer = {"Sum(aggr(Count({<Product_Group=, Creation_Date = p({<Creation_Date = {'>$(=min(Creation_Date))'}>})>}DISTINCT Creation_Date),Customer))>0"}>}DISTINCT Customer)

But the above doesn't work: the result is 0.

Can someone help me figure out why?

Attached are the model & data.

Thanks in advance!

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

The correct expression is this.

COunt({<Customer = {'=Sum(aggr(Count({<Creation_Date = p({<Product_Group=,Creation_Date = {">$(=min(Creation_Date))"}>})>}DISTINCT Creation_Date),Customer))>0'}>}DISTINCT Customer)

Check attached qvw file too.

I have tried to make it more flexible by making the'0' dynamic (Bases on user input)

Hope this solves your issue.

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

4 Replies
MayilVahanan

Hi @dana 

I think, you missed out "=" alone in your expression. Try like below

='# of Customers:'& Count({< Customer = {"=Sum(aggr(Count({<Product_Group=, Creation_Date = p({<Creation_Date = {'>$(=min(Creation_Date))'}>})>}DISTINCT Creation_Date),Customer))>0"}>}DISTINCT Customer)

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
dana
Creator II
Creator II
Author

Hi Mayil,

I fixed it but it doesn't help.

Thanks..

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

The correct expression is this.

COunt({<Customer = {'=Sum(aggr(Count({<Creation_Date = p({<Product_Group=,Creation_Date = {">$(=min(Creation_Date))"}>})>}DISTINCT Creation_Date),Customer))>0'}>}DISTINCT Customer)

Check attached qvw file too.

I have tried to make it more flexible by making the'0' dynamic (Bases on user input)

Hope this solves your issue.

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
dana
Creator II
Creator II
Author

Definitely solved the issue! 

Thank you very much! 

Apparently, the issue was because of the quotes..  I based it on  Karl Pavel's post 

 

SA example.png