Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

By using a date field:

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
michielvandegoo
Specialist
Specialist

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
Specialist III
Specialist III

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

By using a date field:

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


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

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!