Range function

[#1]

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

[vgutkovsky]

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,

[johnw]

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.

[#4]

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.

[#5]

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