Previous year for Pivot Table with Year/Month dimension

[shanemichelon]

Hi all.

I am running into an issue where I have a table with a yearmonth dimension (ie, 2014-Nov,2014-Dec etc).  I have a column that is simply Sum(Sales) for the month sales.  I need a second column showing the sales for the same month of the previous year.

I can't work out how to achieve this.  What I want to do is use set analysis to change the YearMonth dimension to be one year previous. Something like: Sum( {$ <YearMonth={'$(=(Left(YearMonth,4)-1)&'-'&Right(YearMonth,3))'} > } Sales)

I know that doesn't work because the dollar sign expansion is done across the whole table, but I can't work out how to achieve the desired result.

It is driving me nuts because it seems straight forward!

[jagan]

Hi Shane,

This is what I am suggesting you from the beginning, check my first post for the same solution.

Regards,

Jagan.

[shanemichelon]

Hello Jagan.  Yes I am aware that your solution is basically the same as what I did in the end.  However, it is not a solution but a work around.  In my case it will be sufficient, however, what if the client wanted to see the most recent four months, even if that was across year boundaries?  What if the desired result was like this:

YearMonth	Sales	Sales Prev Year
Nov 2013	100	90
Dec 2013	110	105
Jan 2014	123	132
Feb 2014	654	567

The 100 would be sales for Nov 2013, and the 90 would be for Nov 2012.

In this case, my (and Your) solution would not work.  For clarity, the user would need to see both month and year in the first column. Because the year can change from row to row, the dimension must be year and month.

I now believe this is not possible in Qlikview.

[kinahan7]

Is there any way to show the desired outcome in a pivot?  I would like to see the result from the row above in a pivot with the ability to bring in multiple dimensions - I thought ABOVE would do this, but not when including multiple dimensions in the pivot.  This can easily be achieved in DAX using PREVIOUSMONTH.

YearMonth	Sales	Sales Prev Month	Sales Prev Month % Difference	Sales Prev Year
Nov-13	100	0	-100.0%	90
Dec-13	110	100	-9.1%	105
Jan-14	123	110	-10.6%	132
Feb-14	654	123	-81.2%	567