Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nsnybs21qv
New Contributor II

Most recent 8 months date function

Hi

I have a date field [Start Date], and I want to load only records with start date in the  most recent 8 months, including current month. For example, for any date in November, I need to load records from Apr. 1st till Nov. 30th. I'm using this expression

"where [Start Date]>=AddMonths (monthend(today()), - 8);" in load script, but I got Mar. 31st loaded. Any idea why?

Thanks

Yvonne

Tags (1)
1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Most recent 8 months date function

Or try this?

Where [Start Date] >= MonthStart(AddMonths(MonthEnd(Today()), -7))

Here when you do MonthEnd of today you get 11/30/2017 and you are going back 8 months so it is going 30 Apr 2017. As Apr has 31 days when you do >= it pick up 31st too. So try the above expr and see.

5 Replies
vishsaggi
Esteemed Contributor III

Re: Most recent 8 months date function

May be try like

WHERE  [Start Date] > AddMonths (monthend(today()), - 8)

nsnybs21qv
New Contributor II

Re: Most recent 8 months date function

Still has Mar. 31...without "="

vishsaggi
Esteemed Contributor III

Re: Most recent 8 months date function

Or try this?

Where [Start Date] >= MonthStart(AddMonths(MonthEnd(Today()), -7))

Here when you do MonthEnd of today you get 11/30/2017 and you are going back 8 months so it is going 30 Apr 2017. As Apr has 31 days when you do >= it pick up 31st too. So try the above expr and see.

vishsaggi
Esteemed Contributor III

Re: Most recent 8 months date function

I just checked yes do not use that expr. Try the one which i posted just now.

nsnybs21qv
New Contributor II

Re: Most recent 8 months date function

Seems to work now. Thanks!