Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

filter rows with null in calculated expression

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

Untitled-3.jpg

1 Solution

Accepted Solutions
johnw
MVP
MVP

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".

View solution in original post

4 Replies
rupamjyotidas
Specialist
Specialist

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

johnw
MVP
MVP

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".

View solution in original post

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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)

solved.jpg


Best regards

Antonio