9 Replies Latest reply: May 3, 2016 10:27 AM by Marcus Sommer RSS

    Add AGGR function

    Andrew Thomas

      Hi

       

      I need to add the aggr function to the expression below to give a column total in my pivot table for the dimension called 'Region'

       

       

      Sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Salesquantity1 )

      -
      sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} SalesQtyOld)

      +
      sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>}  SalesQtyNew))

       

       

      I'm not sure how to use this function

        • Re: Add AGGR function
          Settu Periyasamy

          Maybe try

           

          =Sum (Aggr (above mentioned expression ,Region))

          • Re: Add AGGR function
            Stefan Wühl

            If you need sum-of-rows in a pivot table, you can do it like

             

            =Sum(

                  Aggr(

                      YourCurrentExpression,

                      ChartDimension1,

                      ChartDimension2,

                      ChartDimension3

                 )

            )

             

             

            Replace YourCurrentExpression and the ChartDimensionsX with your expression and all chart dimension (fields only).

            • Re: Add AGGR function
              Marcus Sommer

              You could try this:

               

              sum(aggr(

              Sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Salesquantity1 )

              -
              sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} SalesQtyOld)

              +
              sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>}  SalesQtyNew))

              , Region))

               

              - Marcus

                • Re: Add AGGR function
                  Andrew Thomas

                  Hi

                   

                  The expression looks ok but I just get zero in the results and no total

                  • Re: Add AGGR function
                    Andrew Thomas

                    Hi

                    Any chance you could help me with this, I'm not sure if I've got the formula correct

                     

                     

                    sum(if(Companycode = 'JP05' or Companycode = 'KR05' or Companycode =  'ID05' or Companycode =  'TW05',
                    Sum(Aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*100*LocalCurrencyRate,
                    Sum(aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*LocalCurrencyRate,LOB,BusinessUnit,NeptuneMarket))))))

                     

                    The expression says it is ok but I get no results

                     

                      • Re: Add AGGR function
                        Marcus Sommer

                        You have with:

                         

                        sum(if(Companycode = 'JP05' or Companycode = 'KR05' or Companycode =  'ID05' or Companycode =  'TW05',
                        Sum(Aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*100*LocalCurrencyRate,
                        Sum(aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*LocalCurrencyRate,LOB,BusinessUnit,NeptuneMarket))))))

                         

                        two aggregations nested without using an aggr(). You could of course nest several aggr-functions but it must be look like:

                         

                        sum(aggr(sum(aggr(sum(aggr(sum(aggr ....

                         

                        In your case try something like this:

                         

                        if(match(Companycode, 'JP05', 'KR05',  'ID05', 'TW05'),
                             Sum(Aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,

                                                                COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue), LOB,BusinessUnit,NeptuneMarket)               

                              * 100 * LocalCurrencyRate,
                             Sum(aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,

                                                                 COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket)

                              * LocalCurrencyRate))

                         

                        whereby it's not quite clear for me what are the aggr-dimensions and what should be on which point multiplied and also where are the difference between then- and the else-expression? Only the 100 - then would be one aggr-expression enough and the condition would be only for the 100 applied.

                         

                        - Marcus