Last year to date (LYTD) flag

[#1]

Hi Community,

in my master calendar I have the following statements, which enables me to do current quarter/year to date vs last quarter/year to date

inyeartodate (Activity.TempDate,Today(), 0) *-1 as Activity.CYTDFlag,

inyeartodate (Activity.TempDate,'2013-02-13',0) *-1 as Activity.LYTDFlag,

inquartertodate (Activity.TempDate,'2014-03-31', 0) *-1 as Activity.CQTDFlag,

inquartertodate (Activity.TempDate,'2013-11-13',0) *-1 as Activity.LQTDFlag

HOWEVER, i have to manually change the dates in bold, whenever i run the script. How can I amend the script so that this is automated?

[vikasmahajan]

You need like wise comparison.  Please check attached qvw.

vikas

[#3]

Is there another way to do this which involves amending my existing calendar script slightly? Although that method you suggested is good, it would involve some substantial changes for me.

[#4]

Hi Hamiltion, Just create the variable for bold values and these variable will change at run time.

LET vLYTD = Date(MakeDate(Year(Today())-1 , Month(Today()) , Day(Today())),'YYYY-MM-DD') ;

LET vCQTD = Date(Floor(QuarterEnd(Today())) , 'YYYY-MM-DD') ;

LET vLQTD = MakeDate( Year(QuarterEnd(Today(),-1)) , Month(QuarterEnd(Today(),-1)) -1 , day(Today())) ;

Use this varaible in the Load statement

[#5]

Instead of putting a static date in, how could I enter 'today minus one quarter' or 'today minus 1 year'

That would give me the date last quarter and year right? But how would I do that?

[#6]

Please check my above expressions in the variables. The variables create the what values you need based on Today.

[Michiel_QV_Fan]

Add these calculations to your master calendar:

YEAR2DATE(Date,  0, 1, $(vDateEnd)) *-1				AS CurYTDFlag,
YEAR2DATE(Date, -1, 1, $(vDateEnd)) *-1				AS LastYTDFlag,

where $(vDateend) is your date field

[#8]

thanks again, if I wanted as always relative to today, would I change it to

YEAR2DATE(Date,  0, 1, $(Today())) *-1				AS CurYTDFlag,
YEAR2DATE(Date, -1, 1, $(Today())) *-1				AS LastYTDFlag,

[#9]

or

YEAR2DATE(Date,  0, 1, Today())) *-1				AS CurYTDFlag,
YEAR2DATE(Date, -1, 1, Today())) *-1				AS LastYTDFlag,

[Michiel_QV_Fan]

Yes, this one should give you the results