Case when.. multiple parameters

user097047

I would like to make a beastmode for a column that contains integers, as well as null values instead of zero. However, my current formula returns null as zero, but the metric that i use is for purchase price variance, and one of my accounts has duplicate values for invoice price and ppv. For example, it would show $65.00 for invoice value and for PPV, because the invoiced items for that account has no standard cost. So essentially, i would like my beastmode to say:

if "ppv" is null, then 0, else return "ppv", and if "ppv" = invoice value, then 0.

GrantSmith

Hi @user097047 

 

If I'm understanding your request correctly you're saying:
-IF PPV is null use 0 in the comparison to the invoice value, otherwise use the PPV value.

    - If the resulting number PPV value is the same as invoice value then return 0 otherwise return the PPV value?

 

With that assumption this should likely work for you:

CASE WHEN IFNULL(`ppv`, 0) = `invoice value` THEN 0 ELSE IFNULL(`ppv`, 0) END

 

If my assumptions are incorrect please let me know.

jaeW_at_Onyx

instead of writing a beast mode with obscure logic, why not clean up the data in ETL?  you don't want your data to be non-intuitive and require a data dictionary in order to use it because that means every future user has to 'know the rules' in order to build or alter a card.

 

change your word order

if "ppv" is null, then 0, else return "ppv", and if "ppv" = invoice value, then 0.

 

CASE

WHEN "ppv" is null THEN 0

WHEN "ppv" = "invoice value" THEN 0

ELSE "ppv"

END