1 Reply Latest reply: Jan 8, 2013 1:22 PM by psyberus RSS

    Set analysis

      Hi

       

      I'm trying to create a calculation for a text box that will return the number of users that have achieved greater than a given number of points/month (held in a variable called vVIP_Points) for the currently selected date range. The table the data comes from gives daily totals of points per user. My approach was to use aggr() to get the total number of points per user in the selected time frame, convert that to an approximate 'monthly' value using interval() and compare the result to the variable as the condition for a set analysis that counts the users but it's not working.

       

      I'm probably missing something vital, being very new to set analysis. Any thoughts/ideas most welcome.

       

      Some example data:

      DateRUser_ID
      Points
      01/01/2013

      1

      3

      01/01/2013

      2

      6
      01/01/201333
      02/01/201318
      02/01/201325
      02/01/201344

       

      My failed attempt:

      =Count({$<Aggr(Sum([Points]),User_ID)*30/Interval(Max(DateR)-Min(DateR),'D')={'>=$(vVIP_Points)'}>} DISTINCT User_ID)

       

      Thanks

      Steve

        • Re: Set analysis

          For anyone with similar challenges, I solved this one without set analysis. My approach here was:

           

          Sum Points for each user over the date range currently selected with this:

          aggr(sum(Points),User_ID)

           

          To get approximate monthly values, the above becomes this:

          aggr(sum(Points)*30/interval(max(DateR)-min(DateR),'D'),User_ID

           

          Then I compare the result to my variable that determines how many points is needed for someone to be a VIP and when true assign a value of 1 which I then sum over:

          Sum(If(aggr(sum(Points)*30/interval(max(DateR)-min(DateR),'D')+1,User_ID)>=$(vVIP_Points),1,0))

           

          This is probably more expensive than a set analysis solution but it's working.