Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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
MVP & Luminary
MVP & Luminary

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.

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

4 Replies

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

Hi Mayil,

I fixed it but it doesn't help.

Thanks..

MVP & Luminary
MVP & Luminary

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.

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Creator
Creator

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