topic How to filter records in straight table in QlikView

How to filter records in straight table

Wed, 04 Nov 2015 09:35:36 GMT

I have a straight table showing 3 dimensions and 10 Expressions..

Now, I'd like to show only records depending on given condition.

In following example, I'd like to show only records where I have "Volumes PY" <> 0 AND "Price gap" <>0

Do I need to set these conditions in each Expression?

thanks

Andrea

Re: How to filter records in straight table

sebastianlettner — Wed, 04 Nov 2015 10:24:55 GMT

Hi,

you could multiply every Expression with "if("Volumes PY" <> 0 AND "Price gap" <>0, 1, 0)" and use "Supress Zero-Values" at the presentation Tab. Or you create a flag in the data model using "if("Volumes PY" <> 0 AND "Price gap" <>0, 1, 0) as flagShowRecord" and multiply every Expression with the flag.

Regards

Sebastian Lettner

Re: How to filter records in straight table

Mark_Little — Wed, 04 Nov 2015 11:29:17 GMT

Hi,

I would add a flag in your data model.

Something like the below

IF([Volumes PY] <> 0 AND [Price gap] <>0,

) AS FLAG,

Then in the set analysis on each expression Add {<Flag ={1}>}

i.e.

SUM({<Flag ={1}>}[Net Sales])

Hope this helps

Mark

Re: How to filter records in straight table

dmac1971 — Wed, 04 Nov 2015 13:01:05 GMT

Yep Marks solution should work for you.

Re: How to filter records in straight table

Wed, 04 Nov 2015 13:08:09 GMT

Hi Mark

thanks for your reply...

I cannot easily (and in a short time...) add a flag to data model and requirement changed to Previous Month instead of Previous Year.. ok that should not be a problem..
directly in the chart

I have to try to solve it directly in the chart.. if possible

I thought there was an option like Filter where [condition satisfied]..

thanks

Re: How to filter records in straight table

Wed, 04 Nov 2015 13:13:30 GMT

Hi,

you can put the condition in the dimensions also as,

if("Volumes PY" <> 0 AND "Price gap" <>0, Plant).

Re: How to filter records in straight table

sunny_talwar — Wed, 04 Nov 2015 13:20:21 GMT

Are you using Plant as your dimension?

then what aalsaif1‌ suggested should work for you. use this as your dimension

If("Volumes PY" <> 0 and "Price gap" <>0, Plant) and then check 'Suppress When Value Is Null' option

Re: How to filter records in straight table

Wed, 04 Nov 2015 13:47:34 GMT

I'm using Plant, Customer and Material as dimensions...

Re: How to filter records in straight table

sunny_talwar — Wed, 04 Nov 2015 15:03:58 GMT

It should still work. Just replace Plant with a calculated dimension -> If("Volumes PY" <> 0 and "Price gap" <>0, Plant) and check 'Suppress When Value Is Null' option

Re: How to filter records in straight table

Mark_Little — Wed, 04 Nov 2015 15:20:13 GMT

Hi Andrea,

I think Sunny's approach is probably going to be your best bet if time is a factor.

If it doesn't work as above you could look at adding a calculated dimension for each, Plant, Customer and Material.

Mark