filter rows with null in calculated expression

[Anonymous]

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

[rupamjyotidas]

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

[johnw · Accepted solution]

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

[Anonymous]

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]

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