Calculating a % based on multiple dates

I would like to create a beast mode that calculates the % of the way we are into a campaign. In Excel, I would calculate this as =(TODAY()-'Start Date') / ('End Date' - 'Start Date')
Here's what I have so far (I want this to return a %):
(SUM(IFNULL(CURDATE(), 0) - IFNULL(`Start Date`, 0)) / NULLIF(SUM(IFNULL(`End Date`, 0) - IFNULL(`Start Date`, 0)), 0))
The formula validates fine but I get an error when I add it to a card.
Best Answers
-
A couple of questions:
- What is the answer you would expect to get when a Campaign has no start date?
- What should be the answer when a campaign has no end date?
In your dataset, what is the level of granularity you have? If this is a campaign level dataset, then a formula to cover the out of bond scenarios (no start date, not yet started, no end date and already ended) first, and then operate at the row level is likely best:
CASE WHEN `StartDate` IS NULL THEN 0 WHEN `StartDate` > CURDATE() THEN 0 WHEN `EndDate` IS NULL THEN 0 WHEN `EndDate` < CURDATE() THEN 1 ELSE ROUND(((UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0)/ROUND(((UNIX_TIMESTAMP(`EndDate`)-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0) END
You could try with DATEDIFF but that one is known to behave oddly sometimes, so the UNIX diff is often preferred and more reliable, feel free to try both.
1 -
You could add a case statement
Case when curdate() > End Date then 1 else … end
where the … is your formula from above
2
Answers
-
SUM(IFNULL(DATEDIFF(CURDATE(), 'Start Date'), 0)) / NULLIF(SUM(IFNULL(DATEDIFF('End Date', 'Start Date'), 0)), 0)
1 -
A couple of questions:
- What is the answer you would expect to get when a Campaign has no start date?
- What should be the answer when a campaign has no end date?
In your dataset, what is the level of granularity you have? If this is a campaign level dataset, then a formula to cover the out of bond scenarios (no start date, not yet started, no end date and already ended) first, and then operate at the row level is likely best:
CASE WHEN `StartDate` IS NULL THEN 0 WHEN `StartDate` > CURDATE() THEN 0 WHEN `EndDate` IS NULL THEN 0 WHEN `EndDate` < CURDATE() THEN 1 ELSE ROUND(((UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0)/ROUND(((UNIX_TIMESTAMP(`EndDate`)-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0) END
You could try with DATEDIFF but that one is known to behave oddly sometimes, so the UNIX diff is often preferred and more reliable, feel free to try both.
1 -
Thanks for all the replies. I ended up going with:
DATEDIFF(CURDATE(),
Start Date
) / DATEDIFF(End Date
,Start Date
)I would like to cap the results at 100%, can I get help with that?
0 -
You could add a case statement
Case when curdate() > End Date then 1 else … end
where the … is your formula from above
2
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 658 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive