Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
3 Keynotes, 100+ Breakout Sessions, 1 New York Times bestseller, and you. QlikWorld Online, May 10-12: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shwetagupta
Partner
Partner

Previous to Previous year till date

Hi All,

I am trying to calculate current year over previous year over previous to previous year.

Where Date1 is my date field, vYear, vMonth, Vquarter are from fiscal calendar.,

For current year : sum({< vYear = {"$(=max(vYear))"}>} invoice_qty)

For Last  year till date :

Sum({<vYear=, vMonth=, VQuarter=,

Date1={">=$(=Date(YearStart(Max(Date1),-1,4)))<=$(=Date(addyears(Max(Date1),-1))) "}>}invoice_qty)

For last to last year till date(LLYTD):

Sum({<vYear=, vMonth=, VQuarter=,

Date1={">=$(=Date(YearStart(Max(Date1),-2,4)))<=$(=Date(addyears(Max(Date1),-2))) "}>}invoice_qty)

In above expression I am not not getting values corresponding to LLYTD either it gives me the whole sum of three years or shows Zero.

Can someone please help!!! what am I missing in the expression

7 Replies
arulsettu
Master III
Master III

can you post your qvf?

shwetagupta
Partner
Partner
Author

Not possible !!!
Can you please suggest if there is anything wrong with the syntax used  for LLYTD

balrajahlawat
Champion
Champion

Say I am having 2012,2013,2014 then my question how you are defining till date today?

Like 01/Jan/2014 till Today : For Max Year

01/Jan/2013 till Today or Till  01/Dec/2013 ??

balrajahlawat
Champion
Champion

for both the mentioned cases, you can try these?

=Sum({<vYear=, vMonth=, VQuarter=,

Date1={'>=$(=YearStart(Year(max(Date1))-2))<=$(=YearEnd(Year(max(Date1))-2))'} >} invoice_qty)

or

=Sum({<vYear=, vMonth=, VQuarter=,

Date1={'>=$(=YearStart(Year(max(Date1))-2))<=$(=Date(max(Date1)))'} >} invoice_qty)

Make sure Date format should be same


shwetagupta
Partner
Partner
Author

till today means :

Say for current years sales from 01/04/2016- 30/08/2016 (Data last loaded)

For Lytd  :  01/04/2015- 30/08/2015

For LLytd : 01/04/2014- 30/08/2014

balrajahlawat
Champion
Champion

Okay, then for Till today you can try Addmonths() like?

date(Addmonths(max(Date1), -12))

or

date(Addmonths(max(Date1), -24))

shwetagupta
Partner
Partner
Author

None of them helped !!
Thanks a lot for the suggestions anyways