topic Re: Nested Aggregation & Cumulation in QlikView

Nested Aggregation & Cumulation

Wed, 23 Oct 2013 18:01:12 GMT

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%

Please please please can you let me know how to calculate these as expressions in qlikview.

Thanks,

Joe

Re: Nested Aggregation & Cumulation

Clever_Anjos — Wed, 23 Oct 2013 18:08:03 GMT

Could you post a sample of your data?

Re: Nested Aggregation & Cumulation

Wed, 23 Oct 2013 18:39:48 GMT

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: Re: Nested Aggregation & Cumulation

Clever_Anjos — Wed, 23 Oct 2013 18:57:37 GMT

Check if solves your needs

Re: Nested Aggregation & Cumulation

Wed, 23 Oct 2013 19:01:55 GMT

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

Sent from my iPhone

Re: Nested Aggregation & Cumulation

Clever_Anjos — Wed, 23 Oct 2013 19:13:42 GMT

Straight Table

Dimension: Band A

Expressions:

sum(Count1)
sum(total <[Band A]> Count1)
Column(1) / Column(2)

Re: Nested Aggregation & Cumulation

Thu, 24 Oct 2013 08:23:45 GMT

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

Anonymous — Thu, 24 Oct 2013 09:02:20 GMT

hi,

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

thks

Re: Nested Aggregation & Cumulation

Thu, 24 Oct 2013 09:06:16 GMT

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

Anonymous — Thu, 24 Oct 2013 14:53:20 GMT

Hi,

to get this tableyou have to:

1. create a straight table

2. dimensions : Payband and Minimum Bands

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

Re: Nested Aggregation & Cumulation

Fri, 25 Oct 2013 08:55:45 GMT

Thank you Jean Marcel.