Year-1 Growth on chart with continuos dimension axes

[#1]

Hi all,

I have built the following chart showing sales growth vs. the same month last year:

This is the expression:

(SUM({$<Type = {'Wine'} >} Sales) / SUM({$<Type = {'Wine'}, Year = {'$(=Year)'}>} Sales)) -1

This works fine providing I select only one year but if I want to show the transition of growth over a number of years, having YYYY-MM as my dimension, the Year-1 part of the expression does not work because more than one year is selected.

Is it possible to show this on a graph in this way and if so what should the expression be?

Many thanks,

APS

[#2]

Hi,

maybe the function MAX() help.

Ex.: (SUM({$<Type = {'Wine'} >} Sales) / SUM({$<Type = {'Wine'}, Year = {'$(=MAX(Year))'}>} CEUs)) -1

[#3]

Does anyone else have a suggestion on how to achieve this?

I tried using the ABOVE function to offset the month by -12 but couldn't get it to work.

[johnw · Accepted solution]

Here's one approach. I'm just creating a separate table to link an AsOfMonth to both the current Month and to the previous year's Month, and then using set analysis to get the correct sums. See attached.

[#5]

John, thanks for the response.

I hadn't thought of creating flags in the script. That makes it very simple to achieve.

Many thanks.

APS

[#6]

Can anyone think of a way to achieve something similar for Year & Quarter, again on a continuous axis?

[johnw]

APS wrote:Can anyone think of a way to achieve something similar for Year & Quarter, again on a continuous axis?

Mmm... do it the exact same way, but with quarters instead of months? Am I misunderstanding the question?

[#8]

Hi John,

I've almost achieved it with quarters using the AsOfDate concept you suggested. However, when I select (for example) 2007,8 and 9 from my calendar, I don't get the previous years sales (where status=previous) for 2007 (see attached qvw - bottom right chart).

In your code you use addmonths - I can't see an addquarters equivalent - is this the problem?

Many thanks for your assistance.

[johnw]

You can't see 2006 because you selected the ACTUAL dates and not the AsOf dates. When you use an AsOf calendar, you need to do the selections from that calendar. I think that's one weakness of the approach. It's not a big deal from the user's perspective, as they won't have any idea which calendar you're having them select from. However, it does mean that your charts all need to be aware of WHICH date range they're really interested in, meaning they may need set analysis to lock in to the current quarter, for instance. Otherwise each quarter would include both the current and previous quarter. There's probably a way to just select which mode you're currently in, though - as of or regular, and use a single calendar. Hmmm. Haven't even thought about that before. Or maybe that would create a circular reference. Hmmm. Maybe something I need to think about.

A quarter is three months, so addmonths(...,-3) gives you the previous quarter.

[#10]

Ok, thanks for the info. It makes sense now. Also, thanks for your assistance with this.