Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Cathalc
Contributor III
Contributor III

Last 12 months to date flag

Hi all,

I've been struggling with this for some time now. 

I want to make date 'format' in my load editor that show the last 12 months to date. I've found several examples in the community of the last year to date (YTD) but the problem with these is, that they start in January of the current year. I want my YTD to show all 12 last months. 

I've tried some things and the closest I seem to get is the following:

If(date >= date(MonthStart(Date(Today()), -12)) AND date < date(MonthStart(Date(Today()))) ,0 , 1) as IsInYTD

I've made a flag here that I can then use in a set expression. The results however seem random.

Anyone see what i'm doing wrong here? Or has anyone done this before?

Thanks and best regards,

Cathal

1 Solution

Accepted Solutions
sunny_talwar

Can you elaborate on what you mean when you say the result are random? Also, you might be able to simplify your script to this

If(date >= MonthStart(Today(), -12) and date < MonthStart(Today()), 0, 1) as IsInYTD

One thing to note is that IsInYTD = 0 when the date is between June 1st 2018 and May 31st 2019. Otherwise it will be 1.

View solution in original post

1 Reply
sunny_talwar

Can you elaborate on what you mean when you say the result are random? Also, you might be able to simplify your script to this

If(date >= MonthStart(Today(), -12) and date < MonthStart(Today()), 0, 1) as IsInYTD

One thing to note is that IsInYTD = 0 when the date is between June 1st 2018 and May 31st 2019. Otherwise it will be 1.