Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Fetching rolling N months of data - how to tackle new year

Dear all,

I have a problem with fetching data of some rolling months.

As soon as the new year started, the formula I wrote is not returning any data (understandably)

RESIDENT Stock

WHERE Month(DateInterpret)>=(Month(today())-6) AND Month(DateInterpret)<=(Month(today())-1)

What is the best common practice in QV to load data for N rolling months?

thank you so much!

1 Solution

Accepted Solutions

Re: Fetching rolling N months of data - how to tackle new year

By using a date field:

where DateInterpret>= monthstart(today(), -6) and DateInterpret< monthstart(today())


talk is cheap, supply exceeds demand
4 Replies
michielvandegoo
Valued Contributor

Re: Fetching rolling N months of data - how to tackle new year

Maybo not a best practice per se but you can create with autonumber a rolling YearMonth ID.

Use autonumber on the yearmonth object from your master calendar, or create this with the combination of year(date)&month(date). This will generate a sequential number starting with 1.

Use this autonumber instead of Month(today()

marcus_malinow
Valued Contributor III

Re: Fetching rolling N months of data - how to tackle new year

Hi Erik,

you could try something like this:

WHERE ((Year(DateInterpret) * 12) + Month(DateInterpret))>= ((Year(today) * 12) + Month(today())-6)

AND ((Year(DateInterpret) * 12) + Month(DateInterpret))<=((Year(today()) * 12) + Month(today())-1)

Marcus

Re: Fetching rolling N months of data - how to tackle new year

By using a date field:

where DateInterpret>= monthstart(today(), -6) and DateInterpret< monthstart(today())


talk is cheap, supply exceeds demand
Not applicable

Re: Fetching rolling N months of data - how to tackle new year

thank you guys, I have to say that both Gysbert's and Marcus' answers are working, just Gysbert's solution looks more proper.

I am not sure how I could have used autonumber in my situation, but still thanks for the input Michiel!

Community Browser