Solved: Previous year for Pivot Table with Year/Month dime... - Qlik Community

shanemichelon · ‎2014-12-24

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!

shanemichelon · ‎2014-12-26

I think I have this all sorted out now. The first issue is that Set analysis is done before the table is calculated. That means that effectively the set being used by the column is set in stone when it is calculated. Therefore the row level dimension cannot be considered as part of the set analysis.

In this case, that means that what is happening is that the set analysis was correctly limiting the results to the previous year data. But then the row level dimension further restricted it to the monthyear of the current year. This resulted in the calculation being on an empty set.

The only solution I can think of is to set it up to that the table is restricted to a single year only. Then use months for the dimension. The LY column can then use the set analysis to select only the previous year instead of the current year. The dimension then restricts this only to the month, which is what we want. To make it work correctly add a calculation condition and message to only show the table if there is only a single year selected. Finally, make the label for the dimension be Max(FYear). Now the table shows the year and the rows are the month:

Attached is the completed sample.

View solution in original post

SunilChauhan · ‎2014-12-24

Sum( {$ <YearMonth={'$(=Date(addmonths(YearMonth,-1)),'YYYY-MMM')''} > } Sales)

Sunil Chauhan

jagan · ‎2014-12-24

Hi,

Create a Month and Year field separately like Jan, Feb, Mar............. and use this as dimension. Now create two expression for Current and Previous year like below

Script:

LOAD

*,

Left(MonthYear, 4) AS Year,

Right(MonthYear, 3) AS Month

FROM DataSource;

Current Year:

Sum( {$ <Year={'$(=Year(Today()))'} > } Sales)

For previous Year:

Sum( {$ <Year={'$(=Year(Today()) - 1)'} > } Sales)

Hope this helps you.

Regards,

Jagan.

shanemichelon · ‎2014-12-24

Thanks For the prompt reply. Unfortunately, it doesn't work. I will try to work out how to add the sample app I am using

shanemichelon · ‎2014-12-24

Thanks Jagan. Unfortunately, I need the YearMonth for the year previous to each row of the table, not the current year. In my real app, I have a calendar table with date, year, etc. But for this table I really need to get the data for the same dimension being from the previous year.

jagan · ‎2014-12-24

Hi,

Can you attach some sample data and your expected output?

Regards,

Jagan.

Report Inappropriate Content · ‎2014-12-24

Hi,

Create a Month and Year field separately like Jan, Feb, Mar............. and use this as dimension. Now create two expression for Current and Previous year like below

Script:

LOAD

*,

Left(MonthYear, 4) AS Year,

Right(MonthYear, 3) AS Month

FROM DataSource;

vMaxyear=Max(year);

vMaxMonth=Max(Month);

Current Year and Month:

Sum( {$ <Year={'$(vMaxyear)'}, Month={'$(vMaxMonth)'} > } Sales)

For previous Year and Month:

Sum( {$ <Year={'$(vMaxyear)-1'}, Month={'$(vMaxMonth)'} > } Sales)

shanemichelon · ‎2014-12-24

Please see the sample app I have attached. In the real app, I have many calendar dimensions such as Fiscal Year, Date, Fiscal Month etc.

shanemichelon · ‎2014-12-24

I have modified the app to add the year,month fields. Please see attached (Still not working)

Report Inappropriate Content · ‎2014-12-24

try the method mentioned in attached sample application

Previous year for Pivot Table with Year/Month dimension