Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
Please find the expression below for the Actual Sales calculation :
Sum(Aggr(if(Measure='Quantity',SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity),if(Measure='Value',SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]),if(Measure='AIMS Units',SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]),SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value])))),Measure,[Calendar Month],Month))
How the YTD Report
The Total Units
for sep should sum sales for month Jul,Aug and Sep ? and so on.
So, If I select [Fiscal Period]=3
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
Sample attached.
Try this:
RangeSum(Before(Sum(Aggr(if(Measure='Quantity', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity), if(Measure='Value', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]), if(Measure='AIMS Units', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]), SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value])))) , Measure, [Calendar Month], Month)), 0, ColumnNo()))
Try this:
RangeSum(Before(Sum(Aggr(if(Measure='Quantity', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity), if(Measure='Value', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]), if(Measure='AIMS Units', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]), SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value])))) , Measure, [Calendar Month], Month)), 0, ColumnNo()))
Thanks ,Yes, it working.
But if I select Specify [Fiscal Period] =3
The Result needs to be :
The Total Units = 18237
But it showing 5072
Try this:
RangeSum(Before(Sum({<[Fiscal Period]>} Aggr(if(Only({<[Fiscal Period]>}Measure)='Quantity', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'}, [Fiscal Period]>} Quantity), if(Only({<[Fiscal Period]>}Measure)='Value', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'}, [Fiscal Period]>} [Sales Value]), if(Only({<[Fiscal Period]>}Measure)='AIMS Units', SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'}, [Fiscal Period]>} [Quantity]), SUM({<[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'}, [Fiscal Period]>} [Sales Value])))) , Measure, [Calendar Month], Month)), 0, ColumnNo())) * Avg(1)
Is it possible to remove this ?
It might be related to the version of QlikView you are using. Can you check to see if 'Suppress Zero Values' is checked on the presentation tab?
So then this is due to the the version of QV you are using. Unfortunately, I won't be able to replicate your issue and hence won't be able to provide a solution which might work for you.
Thank you for your assistance.
Not a problem