Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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.

1 Solution

Accepted Solutions
Highlighted

Or this

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

View solution in original post

5 Replies
Highlighted

Try this

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

Highlighted

Or this

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

View solution in original post

Highlighted

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

Capture.PNG

Highlighted
Master II
Master II

why not use this as a measure

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

Learning never stops.
Highlighted
Creator III
Creator III

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.