Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

simotrab
Contributor II

Aggr() and pivot table

Hi community,

I'm struggling with a pivot table with relative frequencies, and I'm stuck.

My goals is to achieve a simple pivot table that has:

rows: workers

columns: type of sales

measure: percentage of sales for each type of sales.

Here the data:

mov:

Load*Inline

[workerid,sale,type,year

1,5,a,2016

1,20,a,2016

2,1,a,2016

2,8,a,2017

3,9,a,2016

3,2,a,2017

3,1,a,2017

4,4,b,2017

1,5,b,2016

1,5,b,2016

2,3,b,2016

2,5,b,2017

3,9,b,2016

3,3,b,2017

3,9,b,2017

4,1,b,2017

];

Here the result without the percentage but with the simple sum of sales.

one.PNG

So I decided to have the percentage:

the result should be this: the sum of type a is 46 =25+9+12, and for the type b is 44=10+8+21+5.

Then:

workerid        type

                   a                   b

1                 25/46            10/44

2                 9/46               8/44

3                 12/46             21/44

4                    -                  5/44

So I've thought to use the aggr() function in this way:

Sum(sale)/aggr(sum({$<workerid=>} sale),type)

The results are correct, but only if you click one of the workerid, if you do not click one, the result misses some data:

two.PNG

How could I manage to have all the rows filled?

Thanks in advance, attached the app.

Tags (2)
1 Solution

Accepted Solutions

Re: Aggr() and pivot table

Or this

Sum(sale)/aggr(NODISTINCT sum({$<workerid=>} sale),type)

5 Replies

Re: Aggr() and pivot table

Try this

Sum(sale)/Sum(TOTAL <type> {$<workerid=>} sale)

Re: Aggr() and pivot table

Or this

Sum(sale)/aggr(NODISTINCT sum({$<workerid=>} sale),type)

Re: Aggr() and pivot table

Sample attached with both options... but I would suggest to use TOTAL qualifier as Aggr() have slower performance

Capture.PNG

pradosh_thakur
Honored Contributor II

Re: Aggr() and pivot table

why not use this as a measure

Sum(sale)/sum(total<type> sale)

Learning never stops.
simotrab
Contributor II

Re: Aggr() and pivot table

As always, Sunny has the answer! Thanks you very much.

I've selected the second because it works well also in other apps than this one that I gave, but also the first answer fits well.

Community Browser