Tableau formula Conversion to domo question
Hey all, I am trying to create a formulas in my ETL but keeps giving a syntex error.
I'm trying to translate this tableau formula:
if ([Prior 5 Days Avg]) > 0 and sum([Current Day]) > 0 Then
(sum([Current Day]) - ([Prior 5 Days Avg]))/([Prior 5 Days Avg])
elseif sum([Current Day]) = 0 then -.99
else .99
end
Into a DOMO formula:
CASE
WHEN (`Average 5 Day Usage`) > 0 AND SUM(`Current Day Usage *`) > 0
THEN (Sum(`Current Day Usage *`) - (`Average 5 Day Usage`))/(`Average 5 Day Usage`)
WHEN Sum(`Current Day Usage *`) = 0
THEN -0.99
ELSE 0.99
END
But DOMO does not seem to like my above formula.
Any thoughts?
Best Answer
-
@kacy i'm assuming you've created a dataset with 'current day' and average 5 day usage' as columns. you can do that, but I'm not sure I'd recommend this approach because you'll have tons of limited use datasets that only work for one visualization, and eventually it'll become a nightmare to maintain.
but setting that aside, Domo is built on SQL. So you have to convert your query into proper SQL syntax.
looks like you had an extra asterix. if that asterix is intentional, take it out of your dataset, it's unwise to use characters that are reserved for math in column names.
WHEN (`Average 5 Day Usage`) > 0 AND SUM(`Current Day Usage *`) > 0
also you have a problem with your parenthesis / your understanding of aggregation. you can't have half of your formula operate on a SUM and the other half without.
(Sum(`Current Day Usage *`) - (`Average 5 Day Usage`))/(`Average 5 Day Usage`)
you can't do this. Sum(`Current Day Usage *`) this is an aggregation, which means all other columns referred to in your dataset must be aggregated as well.
EITHER
Sum((`Current Day Usage *` - `Average 5 Day Usage`)/ `Average 5 Day Usage`)
OR
(Sum(`Current Day Usage *`) - SUM(`Average 5 Day Usage`))/ sum( `Average 5 Day Usage`)
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"0
Answers
-
@kacy i'm assuming you've created a dataset with 'current day' and average 5 day usage' as columns. you can do that, but I'm not sure I'd recommend this approach because you'll have tons of limited use datasets that only work for one visualization, and eventually it'll become a nightmare to maintain.
but setting that aside, Domo is built on SQL. So you have to convert your query into proper SQL syntax.
looks like you had an extra asterix. if that asterix is intentional, take it out of your dataset, it's unwise to use characters that are reserved for math in column names.
WHEN (`Average 5 Day Usage`) > 0 AND SUM(`Current Day Usage *`) > 0
also you have a problem with your parenthesis / your understanding of aggregation. you can't have half of your formula operate on a SUM and the other half without.
(Sum(`Current Day Usage *`) - (`Average 5 Day Usage`))/(`Average 5 Day Usage`)
you can't do this. Sum(`Current Day Usage *`) this is an aggregation, which means all other columns referred to in your dataset must be aggregated as well.
EITHER
Sum((`Current Day Usage *` - `Average 5 Day Usage`)/ `Average 5 Day Usage`)
OR
(Sum(`Current Day Usage *`) - SUM(`Average 5 Day Usage`))/ sum( `Average 5 Day Usage`)
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"0 -
Thank you! Thanks for the explanation as well that was very helpful!
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 779 Automate
- 191 Apps
- 482 APIs & Domo Developer
- 83 Workflows
- 23 Code Engine
- 41 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 2 AI Projects and Models
- 18 Jupyter Workspaces
- 413 Distribute
- 121 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 145 Manage
- 141 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive