Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Maybe try
=Sum (Aggr (above mentioned expression ,Region))
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).
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
Hi
The expression looks ok but I just get zero in the results and no total
Did you check them one by one to see which one fails?
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
Its ok I've sorted it, it works now
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
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