Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Hide rows in pivot table based on expression

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

3 Replies
captain89
Creator
Creator

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
Not applicable
Author

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
Not applicable
Author

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.