# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:

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

Tags (2)
1 Solution

Accepted Solutions
MVP

## Re: Aggr() and pivot table

Or this

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

5 Replies
MVP

## Re: Aggr() and pivot table

Try this

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

MVP

## Re: Aggr() and pivot table

Or this

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

MVP

## Re: Aggr() and pivot table

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

Honored Contributor II

## Re: Aggr() and pivot table

why not use this as a measure

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

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