topic Re: Hide rows with 0 value in Pivot-Table in QlikView

Hide rows with 0 value in Pivot-Table

kris_vliegen — Tue, 22 Oct 2013 09:05:13 GMT

Hi all,

To change the background-color of my pivot-tabel row by row I use =if(Even(RowNo(TOTAL)), RGB(192,192,192), RGB(165,165,165)) . If I do this for one expression or dimension the 0 values that were hidden show up.

Before:

After:

How Can I hide the empty rows?

Regards,

Kris

Re: Hide rows with 0 value in Pivot-Table

Tue, 22 Oct 2013 09:08:40 GMT

the formulas which you are using on "Expression" tab, same should available on background colour also.

In community you can find more discussion on this..

Karthik

Re: Hide rows with 0 value in Pivot-Table

kris_vliegen — Tue, 22 Oct 2013 09:15:21 GMT

Hi,

I put the formule in the background-expression.

Re: Hide rows with 0 value in Pivot-Table

Tue, 22 Oct 2013 09:31:37 GMT

Tick the box Chart properties -> Dimensions -> Suppress When Value is Null.

Re: Hide rows with 0 value in Pivot-Table

tresB — Tue, 22 Oct 2013 09:32:05 GMT

Hello kris,

Use calculated dimension for 'Datum' like : =If (Not Isnull(Voor.ma) , Datum) , and then check 'Suppress When Value Is Null' for it in the dimension tab.

If Voor.ma is calculated value, then you might have to use Aggr function to achive the same in the calculated dimension.

Re: Hide rows with 0 value in Pivot-Table

kris_vliegen — Tue, 22 Oct 2013 09:37:18 GMT

Hello Tresexco,

The column 'Voorr ma' is a calculated value (see below)

What should i do in the calculated dimension?

Re: Hide rows with 0 value in Pivot-Table

kris_vliegen — Tue, 22 Oct 2013 09:39:04 GMT

Hello,

this is not working. I had done this first but didn't work for my pivot table.

Regards,

Kris

Re: Hide rows with 0 value in Pivot-Table

tresB — Tue, 22 Oct 2013 09:47:06 GMT

Try something like:

=Aggr(If (Not Isnull(YourExpression with firstsortedvalue) , Datum), Station, Omschrijving, Datum)

Assuming you have two dimensions(Fields) in the pivot apart from datum named Station and Omschrijving.

If this does not work, please share your sample app.

Re: Hide rows with 0 value in Pivot-Table

Tue, 22 Oct 2013 10:27:58 GMT

Did you tried to replace nulls with zero, and after, hide zero values ?

Re: Hide rows with 0 value in Pivot-Table

kris_vliegen — Tue, 22 Oct 2013 10:34:12 GMT

I've just tried this: If(isnull calculation, 0 , calculation)

And then in the presentation tab suppress zero-values.

But this doesn't work.

Regards,

Kris

Re: Hide rows with 0 value in Pivot-Table

tresB — Tue, 22 Oct 2013 10:37:26 GMT

Calculated dimension: =

=Aggr(If( Not IsNull (

num(FirstSortedValue(

aggr(sum(Stock15), Station, ArtikelomschrijvingLang, DateTimeNum),

-aggr(DateTimeNum, Station, ArtikelomschrijvingLang, DateTimeNum)

),'#.##0 L')

) ,AROMAR), Station, AROMAR)

Re: Hide rows with 0 value in Pivot-Table

kris_vliegen — Tue, 22 Oct 2013 11:13:20 GMT

Thanks Tresesco,

Now it works :-)))

Regards,

Kris