topic Re: Pivot table + Conditionally Hide Rows in QlikView

Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 07:21:28 GMT

Hello:

I've a pivot table with one dimension Warehouse and three expresions Input, Output and Stock.

I don't want to show rows (warehouse item) where Input = 0 and Output = 0 and Stock = 0. How can I do it?

Thank you.

Joaquín

Re: Pivot table + Conditionally Hide Rows

er_mohit — Fri, 31 May 2013 07:28:29 GMT

Check in dimension tab suppress when value is null

or in presentation tab check

suppress zero value

Re: Pivot table + Conditionally Hide Rows

Sokkorn — Fri, 31 May 2013 07:31:52 GMT

Hi,

Does your (Input, Output and Stock) is Expression or Dimension?

If dimension then you can use "Calculated Dimension" for you [Warehouse Item] with below expression

Aggr(Only({$<Input -={0}, Output -={0}, Stock -={0}>} [Warehouse Item]), [Warehouse Item])

Regards,

Sokkorn

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 07:36:38 GMT

Sokkorn Cheav

They're expressions

Re: Pivot table + Conditionally Hide Rows

Sokkorn — Fri, 31 May 2013 07:38:43 GMT

Hi,

Can you share those expression (Input, Output and Stock)?

Regards,

Sokkorn

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 07:40:54 GMT

er.mohit

Supressing zeros value works, but now I've a fourth expression with a correct value.

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 07:42:10 GMT

Hi Sokkorn Cheav

Yes sum(Inputs), sum(Outputs), sum(Stock)

Re: Pivot table + Conditionally Hide Rows

Sokkorn — Fri, 31 May 2013 07:49:06 GMT

Hi joaquinlr,

Did you try calculated dimension yet? Like this

Aggr(Only({$<Input -={0}, Output -={0}, Stock -={0}>} Warehouse), Warehouse)

Regards,

Sokkorn

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 07:53:38 GMT

Hi Sokkorn Cheav

The calculated dimension works.

Re: Pivot table + Conditionally Hide Rows

Sokkorn — Fri, 31 May 2013 07:54:30 GMT

Hi joaquinlr,

Great to hear that.

Cheer!!!

Sokkorn

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 08:54:36 GMT

Hi Sokkorn Cheav

I have a little problem, with your aggr expression you suggested if Input=0 or Output=0 or Stock=0 the row is hidden.

What I expect is to hide row when Input=0 and Output=0 and Stock=0.

Please, I need more help.

Re: Pivot table + Conditionally Hide Rows

Sokkorn — Fri, 31 May 2013 08:58:41 GMT

Hi,

Above set analysis is AND condition not or.

Can you check your data again or possible attached your app here?

Regards,

Sokkorn

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 09:13:27 GMT

Hi:

The negation is the problem. Now we only show when every column <> 0.

I want to show where one of the expression is <> 0 ... or I want to hide when all the expressions are = 0.

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 09:26:06 GMT

Hi:

I think these images will explain better the problem.

This image is before aggr. The dimension field is Descrip with title Almacén(=Warehouse) with all the zero values; I want to hide row 6 because all the expressions are = 0.

If I apply the calculated dimension the result is not what I expected because only show rows with all

the expression are <> 0

Re: Pivot table + Conditionally Hide Rows

Sokkorn — Fri, 31 May 2013 09:35:43 GMT

Hi,

How many expression you have? You need to apply all expression into calculated dimension.

Ex. I found expression [Stock Transition] then we need to add

Aggr(Only({$<Input -={0}, Output -={0}, Stock -={0}, [Stock Transition] -={0}>} Warehouse), Warehouse) and so on.

Regards,

Sokkorn

Re: Pivot table + Conditionally Hide Rows

Joaquin_Lazaro — Fri, 31 May 2013 09:42:50 GMT

I know ... but if I add [Stock Transition] -={0} the result is wrong, because hide all the rows.

The construction of set analysis will be one of the next:

1) Hide where all expressions equals 0

2) Show where one of the expressions is not 0

Now, we are showing all rows with all the values are not equal 0, just the opposite I want.