Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi Shane,
This is what I am suggesting you from the beginning, check my first post for the same solution.
Regards,
Jagan.
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.
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 |