I have a pivot table with 2 dimensions - a normal dimension vertically and a year dimension running horizontally. 2 expressions - one for Value and another for Growth from the previous year.
The growth from the previous year is calculated thus: =(Column(1)/ After(Column(1))) -1) or alternatively (Column(1)/ Before(Column(1))) -1) depending on the sort order of the years.
Now when that growth calculation returns a null value the chart refuses to suppress it (When all the other columns are also 0 or null). I have tried enabling and disabling suppress missing and suppress null values under Presentation tab.
I have also tried to perform a check and insert Null() when the growth can not be calculated:
=IF((Column(1) = 0 OR Isnull(Column(1))) AND (After(Column(1)) = 0 OR Isnull(After(Column(1)))),Null(), (Column(1)/ After(Column(1))) -1)
It seems as soon as there is a After() or Before() function it will not suppress the nulls no matter how hard you try. It seems to be specific to the before and after functions because as soon as one of them appears in an expression it will not suppress that row even if the row is a null...
If anyone can shed some light on this I would appreciate it, alternatively if there is an easy way to calculate growth dynamically from one expression to the next without using the Before and After functions (and without using set analysis with variables as this needs to run along several years and is not a simple year on year comparison).
Thanks
PLEASE NOTE: I HAVE TRIED USING SUPPRESS NULLS AND SUPPRESS ZEROS UNDER PRESENTATION. A CALCULATED DIMENSION IS ALSO NOT AN OPTION BECAUSE OF OVERHEAD / DATA SIZE.
