# Qlik Sense App Development

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner

## Problem with aggr() in pivot table

Dear all,

I have a problem to calculate 'TOT_OT'

TOT_OT = NB_OT for dimension 'ALLER/RETOUR' = ALLER + RETOUR

It works for 'ALLER' but there is nothing for 'RETOUR'. Is it a bug ??

The formula is : aggr(Sum(if(OTSTYPE = 'OT',1,0)),DPT_NUM_DEP_ARR)

I aggregate the sum on DPT_NUM_DEP_ARR. Here is '14-59'

Why does it work perfectly for the first value 'ALLER' of the dimension 'ALLER/RETOUR' and not for the second one ??

Labels (2)

• ### Pivot Tables

1 Solution

Accepted Solutions
Highlighted
Partner

## Re: Problem with aggr() in pivot table

hi

when using an aggr function , it disregards the dimensions in the table and only uses the dimensions in your function

so it will put the result only once in the other dimensions

you need to change the function to

aggr(nodistinct  Sum(if(OTSTYPE = 'OT',1,0)),DPT_NUM_DEP_ARR)

this will put the same value in all fields

4 Replies
Highlighted
Partner

## Re: Problem with aggr() in pivot table

hi

when using an aggr function , it disregards the dimensions in the table and only uses the dimensions in your function

so it will put the result only once in the other dimensions

you need to change the function to

aggr(nodistinct  Sum(if(OTSTYPE = 'OT',1,0)),DPT_NUM_DEP_ARR)

this will put the same value in all fields

Highlighted
MVP

## Re: Problem with aggr() in pivot table

You can also try this

``Sum(TOTAL <DPT_NUM_DEP_ARR> {<OTSTYPE = {'OT'}>} 1)``
Highlighted
Partner

## Re: Problem with aggr() in pivot table

Yes, it was the problem !! I have forced with nodistinct in the formula and it works perfectly !

Highlighted
Partner

## Re: Problem with aggr() in pivot table

Hi Sunny, i had already tried it but DPT_NUM_DEP_ARR and ALLER/RETOUR are together calculated dimension depended on the same dimension behind. So it added not by row but for all the chart.
I see you on all the post. It was my first post and you answered to me !! Good performance !!
Thanks