Year over Year for the past x months

[intapiuser]

Analytical Need 

Sometimes we wish to see how we are performing in the last x months and comparing that to the previous parallel period.

For example, we wish to see how we perform from Sep last year to Aug this year and compare it with Sep 2 years ago to Aug last year. 

Modeling Challenge

If we use the period-over-period mechanism, we will have the period number (even if displayed by text) ordered from the first one, not necessarily ending in the current period.

Solution

In our example we present a view of 12 months back.

We will create the widget with the following definitions:

1. The x axis is the month (out of the date field)

2. We will filter the x axis for the past 12 months like so:

 Pick Time Frame and then 2 months. 

Image 1. Date filter with 2 months selection

Click on Advanced and change the count to 12 and offset to 0 :

Image 2. Date filter with 12 months selection (including this month)

Click OK.

3. Add 2 values:

• This Year -  sum([Value]) - the regular formula you wish to calculate
• Last Year - (sum([Value]), prev([Months in date],12))  - same formula but with a measured value that uses the PREV function which gives you the option to take 12 months back for each of the dimension (Month) values.

You will get this widget:

Image 3. YoY but with a specific x Axis

But the x axis is misleading because of the year. So we would want to change it to reflect the month name like so:

Image 4. Formatting the x axis labels

And now the X axis will look like this:

Image 5. Final Widget

12 Months YoY Cube.ecdata

test12monthsYOY.dash

[Mitch21]

Would there be a way to change the second values formula, so that rather than presenting only last years monthly values, it presents an average value for the past x years to compare this years values in parallel to?

Thanks