Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
)
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
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
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
)
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!