topic Range function in QlikView

Range function

Mon, 22 Nov 2010 20:19:38 GMT

Hi,

I need to create a chart that shows customers where the sum of the TXN_COUNT is between 0 and 5, 6 and 10, 11 and 15, etc. for a given date range. I think I need an expression that uses one of the range functions but I'm not entirely clear.

Any suggestions would be greatly appreciated.

Thanks,

Shannon

Range function

vgutkovsky — Mon, 22 Nov 2010 20:40:40 GMT

Shannon,

You can either do the work in the script (assigning groups based on the sum) or you can do it dynamically, which will slow down performance a bit. You would need to create a calculated dimension that looks like this:


aggr(
 if(sum(TXN_COUNT<6,
 'Group 1',
 if(sum(TXN_COUNT<11,
 'Group 2',
 'Group 3'
 )
 )
 ,Customer
)

If you want to put this in the script, the solution would be very similar except that instead of aggr over Customer, you would make a GROUP BY Customer.

Regards,

Range function

johnw — Mon, 22 Nov 2010 20:42:58 GMT

Perhaps this?

class(TXN_COUNT,5)

Edit: class(sum(TXN_COUNT),5)

Though it may not produce exactly the results you want. If you need more control over the results, you may need to use a nested if() or a mapping table. No, you don't want one of the rangeXXX() functions. Those are for applying a function to a list of numbers, like rangesum(1,2,3) = 6.

Range function

Mon, 22 Nov 2010 20:47:46 GMT

Thanks. I'll try creating a calculated dimension first ans see if it impacts performance. This is a prototype and still very small so hopefully it won't be an issue.

Range function

Mon, 22 Nov 2010 20:49:02 GMT

Thaks for the response. I'll try your suggestion as well as Vladimir's and see which one works best for I'm trying to do.

Thanks again - Shannon