topic Re: filter rows with null in calculated expression in QlikView

filter rows with null in calculated expression

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

Good morning,

I kindly ask you help.

I can suppress value when is null using a dimension ('cashed') but I cannot find the way to do it using a calculated expression ('average daily cash').

See enclosed sample.

Your help will be appreciated.

Thanks

Antonio

Re: filter rows with null in calculated expression

rupamjyotidas — Wed, 02 Nov 2016 19:27:35 GMT

You can replace the Null Value with Zero instead of hiding them

Re: filter rows with null in calculated expression

johnw — Wed, 02 Nov 2016 19:50:42 GMT

I feel like I must be confused what you're after. It seems your second chart is already what you want? I would write it like this, but it's almost the same idea:

Dimension 1 = site
Dimension 2 = if(cashed,game)
Expression 1 = cashed
Expression 2 = [end date]-[start date]
Expression 3 = column(1)/column(2)

You say you want to suppress when average daily cash is null, but that's apparently just null when cashed is null, and so it seems you're looking for the same thing as the previous chart.

I think it's easiest to null out entire rows by making sure one dimension is null, and suppressing nulls for that dimension. But if you really want to leave your dimensions alone, you'd just need to make sure all rows are null if any are null, something like this:

Dimension 1 = site
Dimension 2 = game
Expression 1 = if(cashed,cashed)
Expression 2 = if(cashed,[end date]-[start date])
Expression 3 = column(1)/column(2)

And suppress zero values.

Edit: Fixed dimension 2. Said "cashed", should have been "game".

Re: filter rows with null in calculated expression

Anonymous — Wed, 02 Nov 2016 20:11:53 GMT

thank you all

on monday when I will be back at the office I will revise you suggestions and let you know

best regards

Antonio

Re: filter rows with null in calculated expression

Anonymous — Mon, 07 Nov 2016 10:15:33 GMT

Hi John,

Thank you for your suggestion.

Moving 'cashed' from dimension to expression and using
the expression 2 = if(cashed,[end date]-[start date])
now allows to filter by expression !!!

Dimension 1 = site
Dimension 2 = game
Expression 1 = cashed
Expression 2 = if(cashed,[end date]-[start date])
Expression 3 = column(1)/column(2)

Best regards

Antonio