qlikview modifier

[kostiskampouris]

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

[marcus_sommer]

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