Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I know there have been several posts on this topic but while I think I'm following the approach used by others, it doesn't seem to want to work for me..
I have a pivot table with 5 dimensions (including one calculated dimension) and 5 expressions. On the Presentation tab, I have Suppress Zero-Values selected. The expression fields includes QE Date, Actual Amount, Projected Amount, Difference and Difference Percentage (absolute value). The Difference Percentage contains the following definition: =(fabs(AQ_Amt-PQ_Amt)/PQ_Amt).
In order to hide the rows with a Difference Percentage under 5%, for each of the 5 expressions, I enabled the Conditional option and put in the following:
=if(fabs((AQ_Amt-PQ_Amt)/PQ_Amt) > .05, <expression definition> ). For example, for the Difference Percentage, I have following condition: =if(fabs((AQ_Amt-PQ_Amt)/PQ_Amt) > .05,(fabs(AQ_Amt-PQ_Amt)/PQ_Amt))
Any thoughts on what I may be doing wrong?
Thanks,
Barry
Hi Barry,
what do you mean when you say hide?
However... can you calculate the expression in the load script like this?
TAB:
LOAD *,
fabs(AQ_Amt-PQ_Amt)/PQ_Amt as exp
INLINE [
Id, AQ_Amt, PQ_Amt
X01, 0, 4
X02, 0, 4
X03, 0, 2
X04, -2, 2
X05, 1, -2
X06, -1, "1,1"
X07, 0, 1
X08, 3, 1
X09, 1, 4
X10, 1, "1,1"
X11, -1, 4
X12, -2, 1
X13, -1, "1,1"
X14, -1, 3
X15, 5, 3
X16, 1, "1,9"
];
then you may use set analysis:
sum({<exp={">=0,5"}>} fabs(AQ_Amt-PQ_Amt))/
sum({<exp={">=0,5"}>} PQ_Amt)
(i have decimal separator=",")
Hi Barry,
To my knowledge, suppressing a row in pivot table happens only when all expression values are null. It doesnt suppress a row even when 'suppress zero values' is marked for one of the expressions.
Use if( isnull(coulnm(n)),null(),condition) for all the non-driving expressions where column(n) is your driving expression column which determines whether a row is to be suppressed or not.
Thanks but I was able to suppress the rows using the method I was trying EXCEPT I had the condition for each expression field in the wrong place. I had it in the Conditional box and I needed to have it in the Definition. Now I have everything that has a difference of less than 5% excluded from the table.