topic Re: Set Analysis with nested AGGR in QlikView

Set Analysis with nested AGGR

dana — Sat, 16 Nov 2024 17:56:11 GMT

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!

Re: Set Analysis with nested AGGR

MayilVahanan — Thu, 22 Oct 2020 06:51:38 GMT

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

Re: Set Analysis with nested AGGR

dana — Thu, 22 Oct 2020 06:58:11 GMT

Hi Mayil,

I fixed it but it doesn't help.

Thanks..

Re: Set Analysis with nested AGGR

kaushiknsolanki — Thu, 22 Oct 2020 07:06:19 GMT

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.

Re: Set Analysis with nested AGGR

dana — Thu, 22 Oct 2020 08:21:19 GMT

Definitely solved the issue!

Thank you very much!

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