Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
dana
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
kaushiknsolanki
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
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
dana
Creator
Creator

Hi Mayil,

I fixed it but it doesn't help.

Thanks..

kaushiknsolanki
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

dana
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