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

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

Fri, 31 Jan 2014 10:28:19 GMT

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!

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

Michiel_QV_Fan — Fri, 31 Jan 2014 10:42:40 GMT

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()

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

marcus_malinow — Fri, 31 Jan 2014 10:49:15 GMT

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

Gysbert_Wassenaar — Fri, 31 Jan 2014 12:45:57 GMT

By using a date field:

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

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

Fri, 31 Jan 2014 15:31:40 GMT

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!