Sum of rows in a pivot Table: looking for an work-around.

[#1]

Hi all,

( I read 22.3 Sum of rows in Pivot Tables on newest reference manual V10, before writing here )

As you can see in my first attachment (bugSumPivotTable.jpg)  in  "2011" column i use follow expression:

if

(  

     (

          CalendarMonthName >  Month( Today())

          AND CalendarMonthName <>'Dec'

     ) ,

     Sum({<FiscalYear = {"$(vFyYear2010"}> } QUANTITY * PRICE), //if the mounth is in the future, I use as prevision of spending same data of the past

     Sum({<FiscalYear = {"$(vFyYear2011)"}>} QUANTITY * PRICE)  //ELSE if the mounth is in the past , I use real data of the current year

)

Total of year 2011 is wrong, it is "12073760" instead of "9621956".

A possible solution ( from reference manual ) is to substitute "if statement" in expression field, with an aggregate function or similar, but I not be able to do it in this case.

Can you help me to create an equivalent statement with aggr function?

Thanks in advance!

EDIT: i try to use this expression with aggr() fuction as suggest in reference manual, but the result is still wrong: no total are showed even if rows are correcty valued (see second attachment:followingSuggestOfReferenceManual.JPG)

aggr(

if (  

( CalendarMonthName >  Month( Today())

AND CalendarMonthName <>'Dec'

AND $(vYearNew)='2011'

) ,

Sum({<FiscalYear = {"$(vFyYear2010)"}>} QUANTITY*PRICE),

Sum({<FiscalYear = {"$(vFyYear2011)"}>} QUANTITY*PRICE)

),

CalendarMonthName \\ dimension used to aggregate

)

[swuehl · Accepted solution]

Well, you want to see sum of rows, rows are given by the aggr() function.

So I believe you need to add a sum() around your aggr(), like

=sum(

aggr(

if (  

( CalendarMonthName >  Month( Today())

AND CalendarMonthName <>'Dec'

AND $(vYearNew)='2011' //da modificare in modo che prenda l'ultimo anno e non ci sia bisogno di modicarlo a mano

) ,

Sum({<FiscalYear = {"$(vFyYear2010)"}>} QUANTITY*PRICE),

Sum({<FiscalYear = {"$(vFyYear2011)"}>} QUANTITY*PRICE)

),

CalendarMonthName \\ dimension used to aggregate

)

)

CalendarMonthName ist your only dimension, right?

Hope this helps,

Stefan

[#3]

Try this:

aggr(

if ( 

( CalendarMonthName >  Month( Today())

AND CalendarMonthName <>'Dec'

AND $(vYearNew)='2011' //da modificare in modo che prenda l'ultimo anno e non ci sia bisogno di modicarlo a mano

) ,

({<FiscalYear = {"$(vFyYear2010)"}>} QUANTITY*PRICE),

({<FiscalYear = {"$(vFyYear2011)"}>} QUANTITY*PRICE)

),

CalendarMonthName \\ dimension used to aggregate

)

[#4]

Many thanks Swuehl !!! It is correct!

p.s.

no, i have others dimension, i add it to aggregate function after CalendarNameMonth ant it works  !

Thanks also to MarcsLiving!