Comparative Gage - What's wrong with my query?
Using the Comparative Gauge,
I wanted to display our performance by whether the number of impressions have increased or decreased.
Only, when I use the following window functions, I get a blank card with a downward arrow.
sum(case when MONTH(`Created Time`) = MONTH(CURRENT_DATE()) then `Likes`+`Shares`+`Comments` end)
sum(case when MONTH(DATE_ADD(`Created Time`, interval 1 month)) = MONTH(CURRENT_DATE()) then `Likes`+`Shares`+`Comments` end)
What I want to do is capture the sum of the previous month in a beastmode, then capture the sum of the current month. Only, the issue arises when I realize that the values will be smaller since the current month is not complete. So I would need to capture where we are at now in comparison to the previous month.
Is this possible?
If you are adding together multiple values and one of them is blank or null, it will not be able to add them all together and just produce a blank value. You would need to do this: IFNULL(likes,0) + IFNULL(shares,0) + IFNULL(comments,0) in order to add them together. If the fields aren't actually null, but blanks, you would need to do CASE statements around them as well to replace that with zero. I find it easier to clean these types of data issues in the ETL so then my beast mode is easier to read.
Also, to deal with the the incomplete months, you can add the DAY function just like you did the MONTH function but say when it is <= instead of = to.
Hope this helps.**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3
Thanks for your input, I'll need to double check to see if i changed my blank null or blank values to 0.
That may be a step I neglected to remember.
I'll get back to you if this resolves my issue.1
So there are nulls in my data but I still can't get it work.0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 80 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive