Aggregation and Pivot Tables Query

[#1]

I have a table which I have already created and lots of set analysis are done into it:

Loan Index

A 55

B 30

C 56

D 50

E 80

How can I actually split the above table into a nicely pivot table:

Index Percentage band Number of entries

0-30 1

31-60 3

61-100 1

Would need to code the percentage band and check the index to ensure they are within the band.

[lironbaram]

hei

attach is an example using CLASS function

the only thing Problematic is the function only works in indentical intrevals

hope its helps you

[#3]

It seems that when I specify

class(aggr(Index,Loan),31)

I get the following:

Class Agg

0<= X <31 , since I did count(Loan) it is basically counting all 5 loans above, therefore I get only

0<= X <31 5

Does not seem to split into 3 different rows

[#4]

I don't think class is going to do exactly what you want here because, like ROIUSER said, class will only show identical intervals (1-5, 6-10, 11-15, etc.)

Maybe something like:

if([Index]>=0 And Index<=30, '0-30',

if([Index] >30 And [Index]<=60, '31-60',

if([Index]>60 And [Index]<=100, '61-100'

)))

I usually don't like putting expressions as dimensions but I don't think this one will be a problem (I've had a few that were huge and made any calculations impossible). I suppose you could put this in the script as a field and just name it 'Index Percentage Band' if you wanted but I must admit I am pretty new to the script so I could be wrong (although I've done this several times so I know I'm not, but I'm pretty new to all of this so I like to cover my tracks).

Hope this helps

[#5 · Accepted solution]

This works for the dimensions bit.

However I still seem to have trouble getting the number of entries displayed in the screen.

What expression should I put to ensure say Index - 55 fits into the 31-60 range and then each entry fits into its own range.

[#6]

Here what I have got:

Indexed LTV Band	All loans
0%-60%	246832
100+%	257966
61%-65%	278360
66%-70%	299406
71%-75%	320237
76%-80%	342216
81%-85%	363971
86%-90%	383681
90%-95%	398878
96%-99%	407186
Total	407186

But when I click on one of the LTV band I get:

Indexed LTV Band	All loans
96%-99%	8308
Total	8308

This seems to be calculated based on 407186 - 398878

Indexed LTV Band	All loans
90%-95%	15197
Total	15197

This seems to be calculated based on 398878 - 383681

Same applied for the other rows. I guess I probably did not put any If conditions into the expression.

Little bit help here would be great.

[#7]

Here what I have got:

Indexed LTV Band	All loans
0%-60%	246832
100+%	257966
61%-65%	278360
66%-70%	299406
71%-75%	320237
76%-80%	342216
81%-85%	363971
86%-90%	383681
90%-95%	398878
96%-99%	407186
Total	407186

But when I click on one of the LTV band I get:

Indexed LTV Band	All loans
96%-99%	8308
Total	8308

This seems to be calculated based on 407186 - 398878

Indexed LTV Band	All loans
90%-95%	15197
Total	15197

This seems to be calculated based on 398878 - 383681

Same applied for the other rows. I guess I probably did not put any If conditions into the expression.

Little bit help here would be great.

[#8]

It looks like you have accumulation turned on or something since your numbers keep getting bigger and bigger and the total is the same as the last record in your chart.

[johnw]

See attached for script solution and calculated dimension solution. I recommend a script solution if the field you're classifying is a real field instead of a calculation itself. Both solutions work fine when you select rows in them. I don't know why you're having problems in your real application. You may need to post something demonstrating the problem.

[#10]

The script does not solve the issue that I have got. I still get the numbers accumulated.

Indexed LTV Band	All loans
0%-60%	246832
61%-65%	267226
66%-70%	288272
71%-75%	309103
76%-80%	331082
81%-85%	352837
86%-90%	372547
90%-95%	387744
96%-99%	407186
Total	407186

I should be expecting the total for 61-65 to be 267226-246832

and 65-60 to be 288272 - 267226 and etc..