Rolling Year/Quarter

[#1]

Hi,

I'm a qlikview newbie and just wondering if there's an easy way to create a rolling year and rolling quarter based on today date?

Thanks for your help

Matt

[#2]

Have a look at the AddMonths() function in the manual.

[#3]

Hello Hellmar,

thank you for your help.
I thought with rolling year the last 12 months are meant. eg. 28.08.2008 - 27.08.2009.
How can I handle this and rolling quarter with addmonths? Isn't addmonths for things like fiscal year calculation?

Regards

[johnw]

Addmonths() is for any case where you need to add or subtract months. A rolling year would involve subtracting twelve months. A rolling quarter would involve subtracting three months. You could set flags in your script, or just use expressions in a chart. The exact expressions depend on exactly what you MEAN by a rolling year and quarter, but here's an example:

sum(if(Date>addmonths(today(),-12),Sales)) // rolling year sales
sum(if(Date>addmonths(today(),-3 ),Sales)) // rolling quarter sales

[#5]

I have a similar requirement. However, I want to do rolling 12 months from a date selected from the calendar object instead of today's date.

[johnw]

So you want to select a Date, then sum all Sales for all Dates for the previous 12 months? And the Date field they're selecting from the calendar is the same Date field as is in the table with the Sales field? Maybe this:

sum({<Date={">$(=addmonths(Date,-12)) <=$(=Date)"}>} Sales)

[#7]

Thanks John!

This seems to calculate the sales amount for the previous 12 months correctly. But, what I would really want to do is to be able to have a graph showing all those 12 months and their sales. Right now, I can see only the latest month on the graph and the sum of sales for all the previous 12 months.

[#8]

I apologize. When I looked carefully, it does show sales for the past months. But, it shows me all the months right from the beginning right till the date chosen.

[#9]

Ok. So I finally got it working. There was something goofy going on with the Month dimension that I was using in the graph. Thanks for the help.

Now, similarly, if I wish to find the prior rolling 12 month period, shouldn't this work?

sum({<Date={">$(=addmonths(Date,-24)) <=$(=addmonths(Date,-12))"}>} Sales)

[#10]

There is a problem with your formula. It assumes that you set the diagram to suppress Zero-Values. This has the side effect that if there are no sales for a given month that month will not be shown.

A workaround is to put the formula as a calculated dimension for the x-axis, combined with Suppress Zero-Values unchecked and Max Visible Number set to 12 or 3 depending on if you want a year or a quarter.

if(Date>addmonths(today(),-12),Date)