How to write the expression for YTD calculation ?

[suvechha_b]

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

How to write the expression for YTD calculation ?

Sample attached.

[sunny_talwar · Accepted solution]

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

[suvechha_b]

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

[sunny_talwar]

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)

[suvechha_b]

Is it possible to remove this ?

[sunny_talwar]

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?

[suvechha_b]

[sunny_talwar]

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.

[suvechha_b]

Thank you for your assistance.

[sunny_talwar]

Not a problem