Set analysis

[#1]

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:

DateR	User_ID	Points
01/01/2013	1	3
01/01/2013	2	6
01/01/2013	3	3
02/01/2013	1	8
02/01/2013	2	5
02/01/2013	4	4

My failed attempt:

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

Thanks

Steve

[#2]

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.