4 Replies Latest reply: Jan 31, 2014 10:31 AM by Erik Furlanis

# 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!

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

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

• ###### 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!