Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
Hi Mayil,
I fixed it but it doesn't help.
Thanks..
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.
Definitely solved the issue!
Thank you very much!
Apparently, the issue was because of the quotes.. I based it on Karl Pavel's post