Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,I am new to qlikview.
I have a table with all the purchases for a time.I have 3 seasons.The current season is the season 3.
I need to calculate the number of the customers who made a purchase this season and have made at least one purchase the previous season (season 2 )as 'existing customers'
Then i need to calculate the number of the customers who made a purchase this season(season 3 ),also made a purchase at season 1 but DID NOT made a purchase at a season 2(regained customers).
The set analysis I use for the existing customers is:
='Existing '&sum(
if (
Aggr(count({<purch_date={"=purch_date>=previusseasonend"}>} memberid),memberid)=1 //bought this season one time
and registrationdate<=previusseasonend //not new member
and Aggr(count({<purch_date={"=purch_date>=previusseasonstart"}>*<fs_dt_date={"=purch_date<previusseasonend"}>} memberid),memberid)>=1 //bought last season
,1,0))
For the existing customers works fine!.But the same for the regained returns 0 which is not right.
='Regained' &sum(
if (
Aggr(count({<purch_date={"=fs_dt_date>=previusseasonend"}>} memberid),memberid)=1 //bought this season one time
and registrationdate<=previusseasonend //not new member
and Aggr(count({<purch_date={"=purch_date>=previusseasonstart"}>*<fs_dt_date={"=purch_date<previusseasonend"}>} memberid),memberid)=0 //didnt buy last season
,1,0))
Can anyone suggest a solution ?Thank you in advance
I think I would try a different approach with something like (Season is created like year within a calendar):
rangesum(
(count({< Season ={1}>} memberid) > 0) * 3,
(count({< Season ={2}>} memberid) > 0) * 2,
(count({< Season ={3}>} memberid) > 0) * 1)
The returning result between 0 and 6 would be unique if there are any purchases and when or (when) not. Depending of your use-case you might wrap it into a pick(match()) and/or within further aggregations with aggr-functions. Whereby I could imagine that a flag-field on this within the script could simplify the calculations within the GUI.
Edit: The results would be negative (between 0 and -6) if the condition is true but it don't changed the logic.
- Marcus