topic Re: Problem with aggr() in pivot table in App Development

Problem with aggr() in pivot table

olivier_vincon — Sat, 16 Nov 2024 05:47:30 GMT

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

Re: Problem with aggr() in pivot table

lironbaram — Mon, 20 May 2019 15:20:50 GMT

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

Re: Problem with aggr() in pivot table

sunny_talwar — Mon, 20 May 2019 15:24:05 GMT

You can also try this

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

Re: Problem with aggr() in pivot table

olivier_vincon — Tue, 21 May 2019 06:28:20 GMT

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

Re: Problem with aggr() in pivot table

olivier_vincon — Tue, 21 May 2019 06:31:14 GMT

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