Question
· May 12, 2016

DeepSee: How to show the growth of a month compared with the month from the last year with Pivot

#InterSystems IRIS BI (DeepSee) #MDX

Hi all,

I have a dashboard which shows the growth of the months compared with the month from the last year.

The way I found to develop that, it was with a kpi. So, I overrode the %OnLoadKPI method with two mdx:

SELECT NON EMPTY [DataD].[H1].[MothYear].Members ON ROWS, [Measures].[CountVisits] ON COLUMNS FROM SubjectAtendimento %FILTER [DataD].[H1].[Year].&[2016]

SELECT NON EMPTY [DataD].[H1].[MonthYear].Members ON ROWS, [Measures].[CountVisits] ON COLUMNS FROM SubjectAtendimento %FILTER [DataD].[H1].[Year].&[2015]

And then, I set the %..data variable with the percent growth of the months for each year.
With kpi is very easy to do that, but, I wouldnt like to do that KPI. Instead, I would like to have a pivot, so t I need to have just one MDX.

Do you have any suggestion?

Discussion (5)0
Log in or sign up to continue
Evgeny Shvarov · May 13, 2016

Hi!

You can make it with ParallelPeriod keyword.

F.e. let's take sales in Samples for the Holefoods cube.

To show YearToYear monthly growth in percent add in the pivot calculated measure "YTY growth" with expression:

(DateOfSale.CurrentMember-ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember))/ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember)

And format: ##.##%

How to use it. 

Place months in a pivot in Rows. Add YTY Calculated Measure to Cols. And add any Measure you want to compare into Measures, f.e. Revenue. 

You'll get following MDX:

 WITH  MEMBER [DateOfSale].[SelectedYear] AS '[DateOfSale].[Actual].[YearSold].$VARIABLE.Year' MEMBER [MEASURES].[YTY growth] AS '(DateOfSale.CurrentMember-ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember))/ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember)',FORMAT_STRING='###.##%' SELECT NON EMPTY {[Measures].[%COUNT],[MEASURES].[YTY GROWTH]} ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]

See the result:

And here are 2011 and 2010 together to prove the measure above works properly:

Hope it helps.

0 0
Evgeny Shvarov · May 13, 2016

Hi!

You can make it with ParallelPeriod keyword.

F.e. let's take sales in Samples for the Holefoods cube.

To show YearToYear monthly growth in percent add in the pivot calculated measure "YTY growth" with expression:

(DateOfSale.CurrentMember-ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember))/ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember)

And format: ##.##%

How to use it. 

Place months in a pivot in Rows. Add YTY Calculated Measure to Cols. And add any Measure you want to compare into Measures, f.e. Revenue. 

You'll get following MDX:

 WITH  MEMBER [DateOfSale].[SelectedYear] AS '[DateOfSale].[Actual].[YearSold].$VARIABLE.Year' MEMBER [MEASURES].[YTY growth] AS '(DateOfSale.CurrentMember-ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember))/ParallelPeriod(DateofSale.YearSold,1,DateOfSale.CurrentMember)',FORMAT_STRING='###.##%' SELECT NON EMPTY {[Measures].[%COUNT],[MEASURES].[YTY GROWTH]} ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]

See the result:

And here are 2011 and 2010 together to prove the measure above works properly:

Hope that helps.

0 0