topic Re: LYYTD Calculation in QlikView

LYYTD Calculation

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

Hi Experts,

Can any one please help me on this.

I am trying to calculate CYYTD and LYYTD.

For CYYTD:

For the Year 2017 I have the data from 1/jan/2017 to 10/sep/2017.

I have tried the below logic

Sum({<[Date]= {">=$(=Date(YearStart(max([Date])),'MM/DD/YYYY'))

<=$(=Date(AddYears(max([Date])),'MM/DD/YYYY'))"},[Revenue]={'Actual'}>}[Income])

Its working fine

For LYYTD:

For the Year 2016 I have the data from 1/March/2016 to 31/Dec/2016.

Out of this I need to show the LYYTD upto 10/sep/2017 i.e., from 1/March/2016 to 10/sep/2017.

Means on the Current YTD what ever the maximum month data I have,

for LYYTD also i need to calculate the data up to that Month only.

Please help me to calulate this LYYTD Expression.

Thanks in advance.

Re: LYYTD Calculation

its_anandrjs — Sat, 28 Oct 2017 13:21:27 GMT

Whatever data you have in your data model when you use Max it gives data based on your data.

I suggest you have to use this in your expression.

If possible share some sample data to have a look.

For CCYTD:-

Sum({<[Date]= {">=$(=Date(AddYears(max([Date]),-1),'MM/DD/YYYY')) <=$(=Date(YearStart(max([Date])),'MM/DD/YYYY'))"},[Revenue]={'Actual'}>} [Income])

For LYYTD:-

Sum({<[Date]= {">=$(=Date(AddYears(max([Date]),-2),'MM/DD/YYYY')) <=$(=Date(AddYears(max([Date]),-1),'MM/DD/YYYY'))"}>} [Income])

Re: LYYTD Calculation

bhavvibudagam — Sat, 28 Oct 2017 14:19:26 GMT

Hi Anand,

I have tried above expressions. But those are not working.

Could you please find the attached application.

The expected output for CYYTD - 1/1/2016 to 10/10/2016 is 17,429 Its getting correctly.

The expected output for LYYTD - 1/1/2015 to 10/10/2015 is 16372 but its showing zero when I select 2016 other wise showing correct.

When I select 2015 the 2014 is the LYTYD right again its showing 0.

There is other months data also under 2015 but need to calculate the data upto the CYYTD months data only i.e, upto october.

!thanks in advance

Re: LYYTD Calculation

its_anandrjs — Sat, 28 Oct 2017 14:40:20 GMT

Can you provide sample in Qlikview file.

Re: LYYTD Calculation

bhavvibudagam — Sat, 28 Oct 2017 14:46:49 GMT

Hi Anand,

Please find the attached app in Qlikview.

Re: LYYTD Calculation

its_anandrjs — Sat, 28 Oct 2017 15:14:44 GMT

In LYTD By pass the Year field

='LYTYD:' &''& Count({<DATE= {">=$(=Date(YearStart(max(DATE),-1),'MM/DD/YYYY')) <=$(=Date(AddYears(max(DATE),-1),'MM/DD/YYYY'))"},Year=>}distinct LWMAINID)

Find the attached also