Hide zero values

[Anonymous]

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?

[Anonymous]

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

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

Regards,

[Or]

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.

[sunny_talwar]

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?

[Anonymous]

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

[Anonymous]

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

[Or]

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))

[Anonymous]

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