Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
suvechha_b
Contributor III

How to write the following expression ?

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?




1 Solution

Accepted Solutions

Re: How to write the following expression ?

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

3 Replies

Re: How to write the following expression ?

Check the attached

1) Changed the expression

2) Changed the variable definition for vPrevFiscalYear

suvechha_b
Contributor III

Re: How to write the following expression ?

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 ?

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