Help with Formulas and Count when <=
Hi all,
I am working on building a card that displays a count of cases that are less than 1, 2 and 7 days old from creation date. Currently using this formula for case age:
SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400
This returns a decimal form in days which is what we want.
Next looking to do a count of cases that fall under 1 day using the calculation above to distinguish how old they are.
I've tried using this equation"
(CASE
WHEN (SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 1 THEN 1
ELSE 0
END)
(CASE
WHEN (SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 2 THEN 1
ELSE 0
END)
(CASE
WHEN (SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 7 THEN 1
ELSE 0
END)
With no luck. Is there a solution in beastmode or does this need to be done in our warehouse with SQL
Comments
-
You just need to move your SUM to the outside of the case statement so that it sums the 'THEN' count like this:
SUM(CASE
WHEN ((UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 1 THEN 1
ELSE 0
END)0
Categories
- 10.7K All Categories
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 101 Apps
- 705 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 23 お知らせ
- 63 Kowaza
- 297 仲間に相談
- 649 ひらめき共有