# 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

## 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.

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:

How could I manage to have all the rows filled?

Thanks in advance, attached the app.

1 Solution

Accepted Solutions
Highlighted
MVP

Or this

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

5 Replies
Highlighted
MVP

Try this

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

Highlighted
MVP

Or this

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

Highlighted
MVP

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

Highlighted
Master II

why not use this as a measure

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

Learning never stops.
Highlighted
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.