Case when.. multiple parameters
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.
Comments
-
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 561 Beast Mode
- 8 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive