Table dimensions as ranges

[#1]

I have a field that contains a range of numbers from 0 well into the hundreds. I'd like to generate a table that looks something like this (the data is oversimplified for the purposes of this example):

	Call Count	Cumulative Call Count	% of Total Calls	Cumulative % of Total Calls
00:00 to 00:10	5	5	5	5
00:11 to 00:20	10	15	10	15
00:21 to 00:30	5	20	5	20
00:31 to 00:40	15	35	15	35
00:41 to 00:50	20	55	20	55
00:51 to 00:60	40	95	40	95
01:00 to ∞	5	100	5	100

I can calculate the single row totals and the cumulative totals for each instance but what I can't figure out is how to have a dimension with custom data such as above. Can this be done?

[swuehl]

You are talking about how to create buckets with one bucket including everything above a threshold?

Like

=If( Field > 20, Dual('More than 20',20), Class(Field, 5) )

as calculated dimension

[sunny_talwar · Accepted solution]

Sample Script:

Table:

LOAD Interval((Ceil(NORMINV(Rand(), 1000, 8)) - 960)/(24*60*60), 'mm:ss') as Call 

AutoGenerate 100000;

Straight table

Dimension:

=If(Call <= (10/(24*60*60)), Dual('00:00 - 00:10', 1),

If(Call <= (20/(24*60*60)), Dual('00:11 - 00:20', 2),

If(Call <= (30/(24*60*60)), Dual('00:21 - 00:30', 3),

If(Call <= (40/(24*60*60)), Dual('00:31 - 00:40', 4),

If(Call <= (50/(24*60*60)), Dual('00:41 - 00:50', 5),

If(Call <= (60/(24*60*60)), Dual('00:51 - 00:60', 6), Dual('01:00 to ∞', 7)))))))

Expressions

1) =Count(Call)

2) =RangeSum(Above(Count(Call), 0, RowNo()))

3) =Count(Call)/Count(TOTAL Call)

4) =RangeSum(Above(Count(Call), 0, RowNo()))/Count(TOTAL Call)

[#4]

This worked perfectly. Now I just need to reverse engineer it so I can understand it better.

Thanks!