topic 13 months rolling with previous year comparison graph in QlikView

13 months rolling with previous year comparison graph

Wed, 22 Sep 2010 16:22:15 GMT

Hi,

I want to have a graph showing current year with previous year lines one on top of each other. This would look similar to this:

This works fine because I have a right function over my periods. Periods are defined as a XXXXMM string (this is how it is defined in the DB). Now, if I want to have a 13 months rolling, one months would be repeated and the data summed together, which is wrong. On the other hand, if we leave the full string, we won't be able to have the two lines above each other. Anyone out there with an idea?

Thanks and regards!

13 months rolling with previous year comparison graph

johnw — Thu, 23 Sep 2010 00:14:55 GMT

Something like this if I understood the question:

YourMainTable:
LOAD
...
,month(date#(Period,'YYYYMM')) as Month
, year(date#(Period,'YYYYMM')) as Year
, date(date#(Period,'YYYYMM'),'MMM YY') as MonthYear
...

AsOf:
LOAD DISTINCT
MonthYear as AsOfMonthYear
,Month as AsOfMonth
,Year as AsOfYear
RESIDENT YourMainTable
;
LEFT JOIN (AsOf)
LOAD AsOfMonthYear as MonthYear
RESIDENT AsOf
;
INNER JOIN (AsOf)
LOAD *
RESIDENT AsOf
WHERE AsOfMonthYear >= MonthYear
AND AsOfMonthYear <= addmonths(MonthYear,12)
;

Dimension 1 = AsOfMonth
Dimension 2 = AsOfYear
Expression = sum(Sales)

13 months rolling with previous year comparison graph

Thu, 23 Sep 2010 09:11:44 GMT

Hi John,

It is not exactly what I am trying to achieve. Here is a screenshot of what it should look like:

I don't think your script does allow this. My problem is how to make a repeated months like 03 in this example unique. I alos forgot to mention that the user can chose tzhe "starting point". In this example the user selected March 2010. The graphs goes then back to March 2009 for the "Current Year" numbers and from March 2009 to March 2008 for the "Previous Year" numbers.

13 months rolling with previous year comparison graph

johnw — Thu, 23 Sep 2010 17:44:37 GMT

OK. User selects March 2010. The "sales current year" line should show March 2009 through March 2010. The "sales previous year" line should show March 2008 through March 2009. Now, let's take for example the data point for January 2010. Do you want that to sum up ONLY the sales from January 2010, or do you want it to sum up ALL sales from January 2009 through January 2010 (which is what I normally understand "rolling 13 months" to mean, but perhaps not in this case).

13 months rolling with previous year comparison graph

Fri, 24 Sep 2010 08:54:57 GMT

I would like to only sum up the sales for January 2010. However my problem is not really how I sum it up but how do I display these two lines above each other. To achieve this, I remove the part "Year" which works fine with 12 months because each is unique. If I have 13 months, one won't be unique and get summed up. Additionally, I don't want to create a value "Sales Previous Year" derived from my values Sales because it would mean having twice as much data.

13 months rolling with previous year comparison graph

johnw — Fri, 24 Sep 2010 18:42:13 GMT

Attached is one approach. I've created an AsOf table. You're forced to choose a single AsOfMonth. When you do so, that AsOfMonth is linked to the current year's 13 months by a YearType of 'Current' and MonthsBack of 0-12. It is also linked to the previous year's 13 months by a YearType of 'Previous' and MonthsBack of 0-12. The chart is then trivial, with dimensions of AsOfMonth and MonthsBack, and expression sum(Sales).

13 months rolling with previous year comparison graph

Wed, 29 Sep 2010 14:53:21 GMT

That works. We would need to replace the "MonthsBack" by something showing the effective month but that shouldn't be the problem. I'll post something when I have it.

Thanks!

13 months rolling with previous year comparison graph

Wed, 29 Sep 2010 15:15:21 GMT

Here we go. I have added a left function on the Month field. However, if MonthsBack = 0 I have a space character after the month name. This makes them unique!

Cheers!

13 months rolling with previous year comparison graph

marcel_olmo — Mon, 20 Dec 2010 16:10:22 GMT

Hey guys, recently I got stuck with the problem you solved in this post.

What I want it's a little bit different.

I don't want to compare between years with an "AsOfMonth" clicked.

My desired result would be a table with months dimension, and the sum of the sales of the last 12 months once I have a Year selected.

13 months rolling with previous year comparison graph

johnw — Mon, 20 Dec 2010 20:24:04 GMT


Marcel Olmo wrote:My desired result would be a table with months dimension, and the sum of the sales of the last 12 months once I have a Year selected.

Sales:
Month, Customer, Sales
2010 Dec, Ann, 20
2010 Dec, Bob, 10
2010 Nov, Ann, 15
etc.

AsOfCalendar:
AsOfYear, AsOfMonth
2010, 2010 Dec
2010, 2010 Nov
etc.

AsOf:
AsOfMonth, Month
2010 Dec, 2010 Dec
2010 Dec, 2010 Nov
...
2010 Dec, 2010 Jan
2010 Nov, 2010 Nov
2010 Nov, 2010 Oct
...
2010 Nov, 2009 Dec
etc.

Dimension = AsOfMonth
Expression = sum(Sales)

If you select AsOfYear 2010, that gives you every AsOfMonth in 2010. Each AsOfMonth in 2010 connects to your actual months to give you your rolling 12 months of data by just using sum(Sales).

13 months rolling with previous year comparison graph

marcel_olmo — Tue, 21 Dec 2010 00:00:03 GMT

Thanks John, now it worked nice.

Cheers!!

Re: 13 months rolling with previous year comparison graph

Thu, 04 Feb 2016 06:34:55 GMT

Hi John Witherspoon

Thanks for your solution it helped me also, here one more request from my side , in the same attached application I want to see a bar chart with last 13 months Variance with its Previous year same month how do I do this, could you please help me on this.

Regards

John

Re: 13 months rolling with previous year comparison graph

darrinturner — Fri, 14 Aug 2020 20:58:50 GMT

Hi, does anyone have an example of this solution. The attached does not load.