# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

## 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

Tags (2)
Not applicable

## 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.