topic Aggregation and Pivot Tables Query in QlikView

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 12:07:21 GMT

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.

Aggregation and Pivot Tables Query

lironbaram — Wed, 23 Feb 2011 12:52:33 GMT

hei

attach is an example using CLASS function

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

hope its helps you

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 15:37:13 GMT

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

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 17:43:41 GMT

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

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 19:14:41 GMT

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.

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 19:25:20 GMT

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.

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 19:25:23 GMT

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.

Aggregation and Pivot Tables Query

Wed, 23 Feb 2011 19:28:35 GMT

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.

Aggregation and Pivot Tables Query

johnw — Wed, 23 Feb 2011 19:34:25 GMT

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.

Aggregation and Pivot Tables Query

Fri, 11 Mar 2011 16:51:33 GMT

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

Aggregation and Pivot Tables Query

johnw — Mon, 14 Mar 2011 17:59:40 GMT


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

The example I gave doesn't accumulate, so your problem lies elsewhere. As Trent said, perhaps you have accumulation turned on. Properties -> Expressions -> Accumulation. It should be "No Accumulation". You could also be accumulating with the expression, like if you're doing some sort of rangesum() with above(). Don't do that. Or maybe you have a data model that forces accumulation, which is a trick I sometimes employ in examples. Don't do that either. But basicaly, you actually have to do something in order to get accumulation. Accumulation isn't the default. So you did something you shouldn't have done.