Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
jmleroux
New Contributor II

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

Aggr_Cumul.png

JM

10 Replies
Employee
Employee

Re: Nested Aggregation & Cumulation

Could you post a sample of your data?

Not applicable

Re: Nested Aggregation & Cumulation

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

Re: Re: Nested Aggregation & Cumulation

Check if solves your needs

Not applicable

Re: Nested Aggregation & Cumulation

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

Sent from my iPhone

Employee
Employee

Re: Nested Aggregation & Cumulation

Straight Table

Dimension: Band A

Expressions:

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

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

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

jmleroux
New Contributor II

Re: Nested Aggregation & Cumulation

hi,

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

thks

Not applicable

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.

jmleroux
New Contributor II

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

Aggr_Cumul.png

JM

Community Browser