Discussion Board for collaboration on QlikView Scripting.
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?
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?
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,|