topic Previous year for Pivot Table with Year/Month dimension in QlikView

Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 06:58:59 GMT

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!

Re: Previous year for Pivot Table with Year/Month dimension

SunilChauhan — Wed, 24 Dec 2014 07:04:23 GMT

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

Re: Previous year for Pivot Table with Year/Month dimension

jagan — Wed, 24 Dec 2014 07:07:40 GMT

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.

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 07:18:54 GMT

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

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 07:22:18 GMT

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.

Re: Previous year for Pivot Table with Year/Month dimension

jagan — Wed, 24 Dec 2014 07:34:57 GMT

Hi,

Can you attach some sample data and your expected output?

Regards,

Jagan.

Re: Previous year for Pivot Table with Year/Month dimension

Wed, 24 Dec 2014 07:38:51 GMT

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)

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 07:47:04 GMT

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.

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 07:55:22 GMT

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

Re: Previous year for Pivot Table with Year/Month dimension

Wed, 24 Dec 2014 07:59:34 GMT

try the method mentioned in attached sample application

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 08:08:17 GMT

Thanks, but the dimension needs to be YearMonth and when changed to that it no longer works:

I think you have the columns wrong. The first column is for the displayed month (ie 2014-Dec), the second column is for the same month, but the previous year.

Re: Previous year for Pivot Table with Year/Month dimension

jagan — Wed, 24 Dec 2014 08:14:21 GMT

Hi,

If you use YearMonth dimension it is difficult to achieve this, since the dimension values are displayed based on the result of the expression. Since you have to use current and previous year data so, you will get YearMonth values of both the years.

So just display the Month in dimension and create two expressions for current and previous year. For current and previous year give dynamic cacptions as Year like Year(Today()) and Year(Today()) - 1.

Regards,

Jagan.

Re: Previous year for Pivot Table with Year/Month dimension

SunilChauhan — Wed, 24 Dec 2014 08:19:38 GMT

try whether you have monthYear as num

by using Num(MonthYear).if it show number then it will work definelty

but if not then try to make it as number and then try formulas

Re: Previous year for Pivot Table with Year/Month dimension

Wed, 24 Dec 2014 08:20:26 GMT

it works see in the image below

and if you want to show this in better format means you want to remove 0 try the expression in text object and design some UI

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 08:20:56 GMT

The problem is that the user could select any set of dates, not just the current year. The user could select Jul-Sep 2014, and therefore the LY column would be for Jul, Aug, Sep 2013

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 08:23:38 GMT

The columns you are using are not what is required. I need column 1 to be for the month shown, and column 2 for the same month of the previous year.

Re: Previous year for Pivot Table with Year/Month dimension

Wed, 24 Dec 2014 08:30:46 GMT

can you share a snap shot of your requirement,Because I didnt get you, what exactly your requirement,

there are multiple ways to do the thing.

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Wed, 24 Dec 2014 08:39:23 GMT

Thanks for your patience.

Sales for 2014-July would be 150, sales for 2013-July would be 277

Sales for 2014-Aug would be 32, Sales for 2013-Aug would be 353

The user could select anything as the date range. It could be 2011-Sep, 2011-Oct-2011-Nov.

Re: Previous year for Pivot Table with Year/Month dimension

tresB — Wed, 24 Dec 2014 09:07:18 GMT

Create a seperate field for last year data in the script itself. Hope, the blog post: Year Over Comparison would give you an idea.

Re: Previous year for Pivot Table with Year/Month dimension

shanemichelon — Sat, 27 Dec 2014 04:51:37 GMT

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.