topic Re: Hide zero values in QlikView

Hide zero values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have been using Qlik for around a month or two now. However, ran into this issue where i can't exclude zero values. I have tried on the presentation tab "Suppress zero values" and also "suppress when value is null"

I think this is because in the rows i would have:

Company Values Income Outcome

a 0 2 NULL

b 12 0 12

c 0 0 Null

d 0 0 Null

The data is loaded in like that and cannot be changed from the load in - I would only want rows C and D to be excluded as it has all zeros and NULL. Although Row A has NULL, it has 2 in income so the whole row should stay.

I am using expressions for values, income and outcome - Below is an example expression for values

sum({<Month={'$(OpMonth)'},[Car]={'Ferrari'},[Tyres]=,[Pirelli]=,[colour]=,[black]={'Apollo'}>} [Begin]))

/1000000

is it possible to do something around this expression so that it treats the zero's as NULL values so that it can be excluded?

Re: Hide zero values

Anonymous — Wed, 01 Nov 2017 11:45:41 GMT

a script upload would be ideal, but why dont you try using a calculated dimension?

IF((Value + Income) > 0,Company,'')

Regards,

Re: Hide zero values

Or — Wed, 01 Nov 2017 11:52:18 GMT

I would consider modifying the data using the NullAsValue script modifier:

NullAsValue Values, Income, Outcome;

Set NullValue = 0;

This would be the way to go if you're treating NULL as a 0 value anyway, and assuming these are original NULL values rather than NULLs from lack of values in a connected table.

Otherwise, you can probably get this to work by using a calculated dimension or by wrapping each expression in a If(This=0, Null(), This) type formula.

Re: Hide zero values

sunny_talwar — Wed, 01 Nov 2017 11:53:48 GMT

Do you want to include company a or not? In your data, you mentioned Outcome as NULL of a, but in your description, it seems like you want to include it.

What is the expression for income?

Re: Hide zero values

Anonymous — Wed, 01 Nov 2017 11:56:41 GMT

i tried wrapping the expression this way:

if(sum({<Month={'$(OpMonth)'},[Car]={'Ferrari'},[Tyres]=,[Pirelli]=,[colour]=,[black]={'Apollo'}>} [Begin]))

/1000000) = 0, NULL()

it just removed every value in that column

Re: Hide zero values

Anonymous — Wed, 01 Nov 2017 11:58:38 GMT

Well in the case of column D - If ALL the data contains 0 or NULL then im happy for the row to disappear.

If it has a value in any of the columns then the whole row has to stay

The expression is almost identical to the one i gave - Just different filtered aspects of

Re: Hide zero values

Or — Wed, 01 Nov 2017 11:58:48 GMT

You forgot the ELSE section, so you're never going to get any values this way. It will resolve to NULL() if there's no value, and since there's no ELSE, it will resolve to Null() if there is a value.

if(sum({<Month={'$(OpMonth)'},[Car]={'Ferrari'},[Tyres]=,[Pirelli]=,[colour]=,[black]={'Apollo'}>} [Begin]))

/1000000) = 0, NULL(),

sum({<Month={'$(OpMonth)'},[Car]={'Ferrari'},[Tyres]=,[Pirelli]=,[colour]=,[black]={'Apollo'}>} [Begin]))

/1000000))

Re: Hide zero values

Anonymous — Wed, 01 Nov 2017 14:25:41 GMT

That didn't work for me. I tried it on this expression. Where am i going wrong. This deletes all the values

IF(sum({<Month={'$(pMonth)'},[Car]={'Ferari'},[Tyres]=,[Pirelli]=,[Colour],[Black]={'Apollo'}>} [Begin])

sum({<Month={'$(pMonth)'},[Car]={'Lambo'},[Tyres]=,[Pirelli]=,[Colour]=,[Black]={'Apollo'}>} [Valuation])

sum({<Month={'$(pMonth)'},[Car]={'Apollo'},[Tyres]=,[Pirelli]=,[Colour]=,[Black]={'Apollo'}>} [Start])

/1000000) = 0, NULL(),

(sum({<Month={'$(pMonth)'},[Car]={'Ferari'},[Tyres]=,[Pirelli]=,[Colour],[Black]={'Apollo'}>} [Begin])

sum({<Month={'$(pMonth)'},[Car]={'Lambo'},[Tyres]=,[Pirelli]=,[Colour]=,[Black]={'Apollo'}>} [Valuation])

sum({<Month={'$(pMonth)'},[Car]={'Apollo'},[Tyres]=,[Pirelli]=,[Colour]=,[Black]={'Apollo'}>} [Start]))

/1000000