Hide rows in pivot table based on expression

[Anonymous]

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

[captain89]

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=",")

[Anonymous]

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.

[Anonymous]

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.