Previous to Previous year till date

[shwetagupta]

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

[arulsettu]

can you post your qvf?

[shwetagupta]

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

[Anonymous]

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 ??

[Anonymous]

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]

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

[Anonymous]

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

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

or

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

[shwetagupta]

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