topic Re: How to write the following expression ? in QlikView

How to write the following expression ?

suvechha_b — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: How to write the following expression ?

sunny_talwar — Fri, 18 Nov 2016 13:25:50 GMT

Check the attached

1) Changed the expression

2) Changed the variable definition for vPrevFiscalYear

Re: How to write the following expression ?

suvechha_b — Fri, 18 Nov 2016 13:46:57 GMT

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

Re: How to write the following expression ?

sunny_talwar — Fri, 18 Nov 2016 15:56:02 GMT

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