13 months rolling with previous year comparison graph

[#1]

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!

[johnw]

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)

[#3]

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.

[johnw]

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).

[#5]

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.

[johnw]

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).

[#7]

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!

[#8]

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!

[marcel_olmo]

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.

[johnw]

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).