Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
You need like wise comparison. Please check attached qvw.
vikas
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.
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
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?
Please check my above expressions in the variables. The variables create the what values you need based on Today.
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
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, |
or
YEAR2DATE(Date, 0, 1, Today())) *-1 | AS CurYTDFlag, | |||
YEAR2DATE(Date, -1, 1, Today())) *-1 | AS LastYTDFlag, |
Yes, this one should give you the results