So what was the problem I was trying to solve.

I need to compute something called – Change Over Previous Year, so for example if my Quarterly data looks like

2004-Q1 | 2004-Q2 | 2004-Q3 | 2004-Q4 | 2005-Q1 | 2005-Q2 | 2005-Q3 | 2005-Q4 | 2006-Q1 | 2006-Q2 |

123 | 456 | 789 | 987 | 654 | 321 | 123 | 456 | 789 | 987 |

I wanted to get the Change of each quarters data for previous year. The output should look like

2004-Q1 | 2004-Q2 | 2004-Q3 | 2004-Q4 | 2005-Q1 | 2005-Q2 | 2005-Q3 | 2005-Q4 | 2006-Q1 | 2006-Q2 |

654-123 | 321-456 | 123-789 | 456-987 | 789-654 | 987-321 | ||||

531 | -135 | -666 | -531 | 135 | 666 |

The 2nd row above shows the actual values that are desirable.

The simplest MDX Query to do the above would be something like this

With Member [Measures].[Value Change] as

‘(([Time].CurrentMember, [Measures].[Value]) – (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value]))’

SELECT {[Time.Time].[Quarter].Members} ON COLUMNS,

{[Product.Product_Package].[product_name].[Patient Visits]} ON ROWS

FROM [CubeName]

WHERE ([Measures].[Value Change])

It was returning me the values as below – (the 1st 4 columns of which are undesirable to me)

2004-Q1 | 2004-Q2 | 2004-Q3 | 2004-Q4 | 2005-Q1 | 2005-Q2 | 2005-Q3 | 2005-Q4 | 2006-Q1 | 2006-Q2 |

123-NA | 456-NA | 789-NA | 987-NA | 654-123 | 321-456 | 123-789 | 456-987 | 789-654 | 987-321 |

123 | 456 | 789 | 987 | 531 | -135 | -666 | -531 | 135 | 666 |

So I changed the MDX query to be this now

With Member [Measures].[Value Change] as

‘(IIF( IsEmpty( (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value]) ), (([Time].CurrentMember, [Measures].[Value]) – (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value]))/ ( (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value])), ([Time].CurrentMember, [Measures].[Value]) – (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value])))’

SELECT {[Time.Time].[Quarter].Members} ON COLUMNS,

{[Product.Product_Package].[product_name].[Patient Visits]} ON ROWS

FROM [CubeName]

WHERE ([Measures].[Value Change])

What I did – was essentially use 2 – functions provided by MDX.

IfEmpty( ) and IIF ( )

I first use the method – IsEmpty( ) on the [Measures].[Value] for the Previous Year’s Time Period – which is – ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember)

and if that is EMPTY – then I do a calculation that is sure to return me Infinity or NAN ( not a Number ) – otherwise I return the original calculated value that I want to.

Once, I get a values of NAN / Infinity from the OLAP Engine – my Downstream Code and Visualizing tool picks that up as a NA and does not show any Number

So initially if my Visualizing tool was showing data as below ( with a different data set from the one shown above in the table )

Now with the modified MDX Query the visualization looks more like this

Now It correctly shows the data as