3 Replies Latest reply: Sep 30, 2011 8:42 AM by Igor.Valle RSS

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

      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

      )

        • Sum of rows in a pivot Table: looking for an work-around.
          Stefan Wühl

          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

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

            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

             

            )