topic Rolling Year/Quarter in QlikView

Rolling Year/Quarter

Thu, 27 Aug 2009 20:19:14 GMT

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

Rolling Year/Quarter

Thu, 27 Aug 2009 20:36:49 GMT

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

Rolling Year/Quarter

Thu, 27 Aug 2009 21:01:17 GMT

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

Rolling Year/Quarter

johnw — Fri, 28 Aug 2009 03:43:57 GMT

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

Rolling Year/Quarter

Thu, 21 Jan 2010 11:54:36 GMT

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.

Rolling Year/Quarter

johnw — Fri, 22 Jan 2010 01:45:40 GMT

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)

Rolling Year/Quarter

Wed, 27 Jan 2010 17:58:46 GMT

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.

Rolling Year/Quarter

Wed, 27 Jan 2010 18:02:27 GMT

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.

Rolling Year/Quarter

Fri, 29 Jan 2010 17:42:23 GMT

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)

Rolling Year/Quarter

Thu, 04 Feb 2010 15:10:26 GMT

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)

Rolling Year/Quarter

Mon, 15 Feb 2010 21:50:55 GMT

I am working on a similar report but would like to do the same calculation but then on weeks in the year.

In my script I have added to my calender a year_weeknr (e.g. 2010-2 i.e. year 2010 weeknr 2) and Previousyear_weeknr (e.g. 2009-2) combination.

I guess sinds there is no addweeks function or is there a way that I can calculate the (start) date from a weeknr, year combination within setanalyses?

Jolanda

Rolling Year/Quarter

johnw — Tue, 16 Feb 2010 19:29:57 GMT

The addweeks() function doesn't exist, but to add a week, add 7. The date is internally stored as the number of days since 12/30/1899... or some date close to that. I forget.

I don't quite understand what you're trying to do other than that. However, I would recommend using the dual() function to assign the start date for the week as the underlying numeric value for your week number field. Alternatively, do what I do in my applications, and use the start date for the week INSTEAD of using a week number.

Rolling Year/Quarter

Wed, 17 Feb 2010 15:28:28 GMT

Thank you, this helped me a bit more in the right direction. However I am not quite there yet.

I have added the following to my calendar:

e.g.

Weeks table

YearWeek, Weekstartdate, LYearWeek, LYWeekstartdate

2010-02, 11-02-2010, 2009-02,05-01-2009

etc.

Now I have a pivot table like this:

My final goal is a pivot table calculating the turnover rate.

The formula the business is using is:

(The total cost of inventory from lastyear, end of lastweek until this year end of this week)

devided by

(The total value of inventory from lastyear, end of lastweek until this year end of this week)

my output will look more or less like this:

		2010	2009
	weeknr	costvalue (accumulated for one year)>	(Same will apply to other years for which there exists data)
Shop 1	2	[to calculate here the total cost supplied from 2009 week 3 to this week]
Shop 1	3	[to calculate here the total cost supplied from 2009 week 4 to this week]
Shop 1	etc	i.d.
Shop 2	etc	i.d.

(and so forth also for other shops)

I have now the following formula to calculate the total cost of inventory:

SUM( {<Date=>} IF ((Date> '$(#LYWeekstartdate)' AND Date<= '$(#Weekstartdate)', CostAmount))

However, in the setanalyses, I can not get the syntax correct now to select the whole range of dates between these 2 dates and that for a specific shop and year (see pivot table).

Can I reference field values in this way in set analyses?

Jolanda

Rolling Year/Quarter

Wed, 17 Feb 2010 15:28:42 GMT

Thank you, this helped me a bit more in the right direction. However I am not quite there yet.

I have added the following to my calendar:

e.g.

Weeks table

YearWeek, Weekstartdate, LYearWeek, LYWeekstartdate

2010-02, 11-02-2010, 2009-02,05-01-2009

etc.

Now I have a pivot table like this:

My final goal is a pivot table calculating the turnover rate.

The formula the business is using is:

(The total cost of inventory from lastyear, end of lastweek until this year end of this week)

devided by

(The total value of inventory from lastyear, end of lastweek until this year end of this week)

my output will look more or less like this:

		2010	2009
	weeknr	costvalue (accumulated for one year)>	(Same will apply to other years for which there exists data)
Shop 1	2	[to calculate here the total cost supplied from 2009 week 3 to this week]
Shop 1	3	[to calculate here the total cost supplied from 2009 week 4 to this week]
Shop 1	etc	i.d.
Shop 2	etc	i.d.

(and so forth also for other shops)

I have now the following formula to calculate the total cost of inventory:

SUM( {<Date=>} IF ((Date> '$(#LYWeekstartdate)' AND Date<= '$(#Weekstartdate)', CostAmount))

However, in the setanalyses, I can not get the syntax correct now to select the whole range of dates between these 2 dates and that for a specific shop and year (see pivot table).

Can I reference field values in this way in set analyses?

Jolanda

Rolling Year/Quarter

Mon, 20 Dec 2010 12:06:21 GMT

Hello,
I have a similar problem. I want to show values (for example Sales) for previous quarter.

If there is no selection he will have the sales for this quarter and the previous quarter. If there is selection then it gives the results for the same time in previous quarter. For example if he selects month 6 he will have the sales for month 6 and for previous period he will have sales in month 3.

How can I achieve that?

Re: Rolling Year/Quarter

Anonymous — Wed, 15 May 2013 13:03:33 GMT

Hi John,

Firstly, thanks for such a wonderful piece of advices that you have given above.

Now I'm a little bit of different requirement. I've the year(2011,2012 etc.) and its relevant quarters(1,2,3,4) is present in the raw data and I'm trying to calculate Year on Year (YoY) calculation where if I select the year as 2013 then it should show me the data from 2012 Q1 to 2013 Q1. Similarly I need to have the Quarter on Quarter (QoQ).

Any suggestion you can provide, would be a great help.

Thanks!!!