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

[#1]

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!

[Michiel_QV_Fan]

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]

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

[Gysbert_Wassenaar · Accepted solution]

By using a date field:

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

[#5]

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!