Add AGGR function

[#1]

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

[settu_periasamy]

Maybe try

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

[swuehl]

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).

[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

[#5]

Hi

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

[robert_mika]

Did you check them one by one to see which one fails?

[marcus_sommer · Accepted solution]

What dimensions are included in this chart? And check the syntax of your expression - I have only copy/paste your snippet but if I look on them I see two closing brackets on the third expression-part.

- Marcus

[#8]

Its ok I've sorted it, it works now

[#9]

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

[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