Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
How to write the following expression ?
The expression
RangeSum(Before(Sum(Aggr(if(Measure='Quantity', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity),
if(Measure='Value', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]),
if(Measure='AIMS Units', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]),
SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value]))))
, Measure, [Calendar Month], Month,[Fiscal Year])), 0, ColumnNo()))
How to calulate the PY Actual ?
sample attached?
My bad, forgot to add the RangeSum(Before())
RangeSum(Before(Sum({<[Fiscal Year]={$(vPrevFiscalYear)}>} Aggr(if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='Quantity', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity), if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='Value', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]), if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='AIMS Units', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]), SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value])))) , Measure, [Calendar Month], Month,[Fiscal Year])), 0, ColumnNo()))
Check the attached
1) Changed the expression
2) Changed the variable definition for vPrevFiscalYear
But the Prev Yr Actual is not summing up like the below:
The report should , show from Jul to Sep
and the Total Units should be
Jul Aug Sep
5041 5041 +8124 5041 +8124+5072
And if I dont select any [Fiscal Period ]
The output :
Jul Aug Sep Jun
5041 5041 +8124 5041 +8124+5072 ......... 5041 +8124+5072 ......... +9007
My bad, forgot to add the RangeSum(Before())
RangeSum(Before(Sum({<[Fiscal Year]={$(vPrevFiscalYear)}>} Aggr(if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='Quantity', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity), if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='Value', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]), if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='AIMS Units', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]), SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value])))) , Measure, [Calendar Month], Month,[Fiscal Year])), 0, ColumnNo()))