10 Replies Latest reply: Oct 25, 2013 4:55 AM by Joe Berry

Nested Aggregation & Cumulation

I have a dataset with two dimensions and one simple expression (count).

I need to add an expression that is the total of the counts within one of the dimensions. So this is what I have, with Aggregate1 defined as

Aggr(count(ID),Payband)

 Payband Minimum Bands Count1 Aggregate1 < 1.5 400000 44 44 1.5 - 2 400000 53 3215 1.5 - 2 500000 1451 - 1.5 - 2 600000 902 - 1.5 - 2 750000 470 - 1.5 - 2 1000000 288 - 1.5 - 2 1500000 51 -

What I need is two things. I need the 3215 to show for all of Payband, but then I also need to show the cumulative distribution as follows:

 1.5 - 2 400000 53 3215 98% 1.5 - 2 500000 1451 3215 53% 1.5 - 2 600000 902 3215 25% 1.5 - 2 750000 470 3215 11% 1.5 - 2 1000000 288 3215 2% 1.5 - 2 1500000 51 3215 0%

Thanks,

Joe

• Re: Nested Aggregation & Cumulation

Could you post a sample of your data?

• Re: Nested Aggregation & Cumulation

Can't attach the excel but a copy and paste below:

 Band A Band B Count1 < 1.5 4000 183.6 1.5 - 2 4000 187.2 1.5 - 2 5000 3936.6 1.5 - 2 6000 2604.6 1.5 - 2 7500 1463.4 1.5 - 2 10000 937.8 1.5 - 2 15000 201.6 2 - 2.5 4000 1.8 2 - 2.5 5000 36 2 - 2.5 6000 48.6 2 - 2.5 7500 95.4 2 - 2.5 10000 113.4 2 - 2.5 15000 50.4 2 - 2.5 20000 100.8 2.5 - 3 5000 12.6 2.5 - 3 6000 25.2 2.5 - 3 7500 54 2.5 - 3 10000 46.8 2.5 - 3 15000 27 2.5 - 3 20000 84.6

• Re: Nested Aggregation & Cumulation

I don't have a qv license can you just show me the syntax?

Sent from my iPhone

• Re: Nested Aggregation & Cumulation

Straight Table

Dimension: Band A

Expressions:

1. sum(Count1)
2. sum(total <[Band A]> Count1)
3. Column(1) / Column(2)
• Re: Nested Aggregation & Cumulation

Hi Anjos,

Apologies but I did not give you the detail at the right level, what I provided above was the data once is had been summarised. The source data we are importing into Qlikview looks more like this (as a sample):

 ID Payband Minimum Bands 1 1.5 - 2 1000000 2 2 - 2.5 750000 3 1.5 - 2 1000000 4 1.5 - 2 1000000 5 < 1.5 400000 6 < 1.5 400000 7 < 1.5 400000 8 < 1.5 400000 9 < 1.5 400000 10 1.5 - 2 600000 11 2 - 2.5 2000000 12 4 - 5 2000000 13 1.5 - 2 750000 14 1.5 - 2 1000000

What we want to do is create a table that then aggregates to the level below (this is pretty simple, just by having Payband and Minimum Bands as your dimensions and count(ID) as your expression).

 Payband Minimum Bands Total < 1.5 400000 5 1.5 - 2 600000 1 1.5 - 2 750000 1 1.5 - 2 1000000 4 2 - 2.5 750000 1 2 - 2.5 2000000 1 4 - 5 2000000 1

What I then need are two expressions, one which shows the total number of IDs per payband, and the other that shows the cumulative percentage of each level within the aggregation, so this:

 Payband Minimum Bands Total Total per Payband Cumulative % < 1.5 400000 5 5 0% 1.5 - 2 600000 1 6 83% 1.5 - 2 750000 1 6 67% 1.5 - 2 1000000 4 6 0% 2 - 2.5 750000 1 2 50% 2 - 2.5 2000000 1 2 0% 4-5 2000000 1 1 0%

There is a kind of recursive calculation that goes on for the Cumulative % in the final column, so it needs to add the total of every row that exists in the Payband.

Is this possible in Qlikview?

Thanks so much for your help on this so far.

Joe

• Re: Nested Aggregation & Cumulation

hi,

can you explain plz cumulative % column (exple for payband 1.5-2)

thks

• Re: Nested Aggregation & Cumulation

Sure, looking at Payband 1.5-2:

For the first row in this group it is (6-1) / 6

For the second row it is (6-(1+1)) / 6

For the third row it is (6-(1+1+4)) / 6

So it is the cumulative total per Payband, but as the residual percentage.

• Re: Re: Nested Aggregation & Cumulation

Hi,

to get this tableyou have to:

1. create a straight table

2. dimensions : Payband and Minimum Bands

3.

Expression 1 : Total = count(ID)

Expression 2 : Total per Payband = count(TOTAL <Payband> ID)

Expression 3 : Cumul = rangesum(above(count(ID),0NoOfRows()))

Expression 4 : Cumulative % = (column(2)-column(3))/column(2)

4. Presentation hide column 3

End

JM

• Re: Nested Aggregation & Cumulation

Thank you Jean Marcel.