Problem with aggr() in pivot table

[olivier_vincon]

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

[lironbaram · Accepted solution]

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 

[sunny_talwar]

You can also try this

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

[olivier_vincon]

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

[olivier_vincon]

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