Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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)

PaybandMinimum BandsCount1Aggregate1
< 1.54000004444
1.5 - 2400000533215
1.5 - 25000001451-
1.5 - 2600000902-
1.5 - 2750000470-
1.5 - 21000000288-
1.5 - 2150000051-

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 - 240000053321598%
1.5 - 25000001451321553%
1.5 - 2600000902321525%
1.5 - 2750000470321511%
1.5 - 2100000028832152%
1.5 - 215000005132150%

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

Thanks,

Joe

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

Aggr_Cumul.png

JM

View solution in original post

10 Replies
Clever_Anjos
Employee
Employee

Could you post a sample of your data?

Not applicable
Author

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

Band ABand BCount1
< 1.54000183.6
1.5 - 24000187.2
1.5 - 250003936.6
1.5 - 260002604.6
1.5 - 275001463.4
1.5 - 210000937.8
1.5 - 215000201.6
2 - 2.540001.8
2 - 2.5500036
2 - 2.5600048.6
2 - 2.5750095.4
2 - 2.510000113.4
2 - 2.51500050.4
2 - 2.520000100.8
2.5 - 3500012.6
2.5 - 3600025.2
2.5 - 3750054
2.5 - 31000046.8
2.5 - 31500027
2.5 - 32000084.6
Clever_Anjos
Employee
Employee

Check if solves your needs

Not applicable
Author

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

Sent from my iPhone

Clever_Anjos
Employee
Employee

Straight Table

Dimension: Band A

Expressions:

  1. sum(Count1)
  2. sum(total <[Band A]> Count1)
  3. Column(1) / Column(2)
Not applicable
Author

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):

IDPaybandMinimum Bands
11.5 - 21000000
22 - 2.5750000
31.5 - 21000000
41.5 - 21000000
5< 1.5400000
6< 1.5400000
7< 1.5400000
8< 1.5400000
9< 1.5400000
101.5 - 2600000
112 - 2.52000000
124 - 52000000
131.5 - 2750000
141.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).

PaybandMinimum BandsTotal
< 1.54000005
1.5 - 26000001
1.5 - 2 7500001
1.5 - 210000004
2 - 2.57500001
2 - 2.520000001
4 - 520000001

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:

PaybandMinimum BandsTotalTotal per PaybandCumulative %
< 1.5400000550%
1.5 - 26000001683%
1.5 - 2 7500001667%
1.5 - 21000000460%
2 - 2.57500001250%
2 - 2.52000000120%
4-52000000110%

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

Anonymous
Not applicable
Author

hi,

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

thks

Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Aggr_Cumul.png

JM