Aggragate problem on pivot table

[#1]

Hi to all,

i have a problem on aggregate function. In the attached file a small example. The column NOE I have the values of CoGe that in 2009 have sales = 0.

The if clause, of course, don't work in the total. I tried in different way but without getting it to work.

Thanks!

Andrea

[johnw · Accepted solution]

Maybe this?

sum(aggr(if(sum({<Anno={2009}>} FatNetto),0,sum({<Anno={2010}>} FatNetto)),CodCliRif,CoGe,Mese))

The general form is this:

sum(aggr( your current expression ,dimension1,dimension2,etc.))

However, it doesn't appear to be able to reference the columns, probably because the aggr() doesn't realize you're specifying the exact same set of columns as the pivot table specifies. That's why I replaced column references with recalculations.

[#3]

Hi John,

thanks! I have tried so many times that I don't know why I didn't get it!

Andrea