Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Last year to date (LYTD) flag

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?

Tags (1)
14 Replies
vikasmahajan
Not applicable

Re: Last year to date (LYTD) flag

You need like wise comparison.  Please check attached qvw.

vikas

Not applicable

Re: Last year to date (LYTD) flag

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.

Not applicable

Re: Last year to date (LYTD) flag

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

Not applicable

Re: Last year to date (LYTD) flag

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?

Not applicable

Re: Last year to date (LYTD) flag

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

michielvandegoo
Not applicable

Re: Last year to date (LYTD) flag

Add these calculations to your master calendar:

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

where $(vDateend) is your date field

Not applicable

Re: Last year to date (LYTD) flag

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

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

Re: Last year to date (LYTD) flag

or

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

Re: Last year to date (LYTD) flag

Yes, this one should give you the results