Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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
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 ??

prob_qlik_aggr_pivot_table.jpg

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
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 

View solution in original post

4 Replies
Highlighted
Partner
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 

View solution in original post

Highlighted

Re: Problem with aggr() in pivot table

You can also try this

Sum(TOTAL <DPT_NUM_DEP_ARR> {<OTSTYPE = {'OT'}>} 1)
Highlighted
Partner
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
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