# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
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
MVP

## Re: How to write the following expression ?

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
MVP

## Re: How to write the following expression ?

Check the attached

1) Changed the expression

2) Changed the variable definition for vPrevFiscalYear

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

MVP