Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Hide zero values

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?

7 Replies
Highlighted
Creator III
Creator III

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

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

Regards,

Highlighted
Specialist III
Specialist III

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.

Highlighted

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?

Highlighted
Contributor II
Contributor II

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

Highlighted
Contributor II
Contributor II

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

Highlighted
Specialist III
Specialist III

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

Highlighted
Contributor II
Contributor II

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