topic Re: How to write the expression for YTD calculation ? in QlikView

How to write the expression for YTD calculation ?

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

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.

Re: How to write the expression for YTD calculation ?

sunny_talwar — Fri, 18 Nov 2016 11:58:21 GMT

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

Re: How to write the expression for YTD calculation ?

suvechha_b — Fri, 18 Nov 2016 12:06:11 GMT

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

Re: How to write the expression for YTD calculation ?

sunny_talwar — Fri, 18 Nov 2016 12:09:26 GMT

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)

Re: How to write the expression for YTD calculation ?

suvechha_b — Fri, 18 Nov 2016 12:13:28 GMT

Is it possible to remove this ?

Re: How to write the expression for YTD calculation ?

sunny_talwar — Fri, 18 Nov 2016 12:15:11 GMT

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?

Re: How to write the expression for YTD calculation ?

suvechha_b — Fri, 18 Nov 2016 12:19:18 GMT

Re: How to write the expression for YTD calculation ?

sunny_talwar — Fri, 18 Nov 2016 12:21:27 GMT

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.

Re: How to write the expression for YTD calculation ?

suvechha_b — Fri, 18 Nov 2016 12:23:36 GMT

Thank you for your assistance.

Re: How to write the expression for YTD calculation ?

sunny_talwar — Fri, 18 Nov 2016 12:23:58 GMT

Not a problem

Re: How to write the expression for YTD calculation ?

suvechha_b — Fri, 18 Nov 2016 12:31:01 GMT

Please can you help to get the Prev Yr Actuals .

See the question.

How to write the following expression ?