Rolling 12 Months

Tue, 12 Nov 2013 22:30:10 GMT

Hello Experts,

I believe that I have gone through all of the rolling 12 month discussions, but none of the solutions seemed to work with the large and complex application that I inherited. I believe that I have another option that I would like to pose to the Qlik Community for feedback. It seems to work, but I'm wondering if there are any flaws in my logic, or possible shortfalls.

I started by adding the following line to my load script (my script already included a variable for today's date) :

Num((Num(Date(MonthStart($(vToday)))) - Num(Date(MonthStart(Date,0)))) / 30), '#,##0') as [Month Counter]

After reloading my application, I added a formula that makes use of set analysis. The formula looks something like this:

count({$<[Month Counter]={'>$(=min([Month Counter])-1)<$(=min([Month Counter])+12'}>} DISTINCT Patients.unique_id)

Again, this combination of script and set analysis seems to work, but I seem to have some data issues. When I run a query directly against the SQL source table, my counts are slightly different. I'm wondering if my setup for the "month counter" could be to blame. Any feedback or insights would be greatly appreciated.

Thank you,

Jason

topic Rolling 12 Months in QlikView

Rolling 12 Months