Is there a way in Domo to plot date and time against a *fixed and gapless* x-axis?

DataSquirrel

Is there a way in Domo to plot date and time against a *fixed and gapless* x-axis?

 

When we plot by hour, we want all 24 hours, not just any that are present in our DataSet. For the gaps, we would like a 0.

I set up a simple dummy DataSet, and can't find a way to make this work.

 

I found this thread from four years ago:

https://dojo.domo.com/t5/Card-Building/X-Axis-Interval-hourly/m-p/11271

 

Rings a bell, we're already injecting 0-value placeholder rows to populate bins in some of our DataSets. But what's cropped up now are cases where filtering excludes those rows, breaking the x-axis' continuity. Imagine this simple DataSet:

 

When                    What        For           How Many
2020-06-09 00:05:00     Receive     Building A          1
2020-06-09 00:05:00     Inspect     Main                0  <-- Placeholder example
2020-06-09 00:07:00     Receive     Building A          3
2020-06-09 00:12:00     Reject      Building A          4
2020-06-09 00:13:00     Pack        Main                5

 

To make the 0-value injection hack work, in this case, we would need to add 0 value rows for *every* combination of hour, What and For. Leaving out that the data explodes out, there are some problems with this:

 

• It's no longer straightforward to change intervals.
• What and For, in this example, are both dynamic and continually evolving. Meaning, new items appear all the time. To make the system work properly, we would then have to retrofit blank rows back to our start date for new "What" and "For" values.
• And "What" and "For" are just made-up, we have more such fields in the real world, only amplifying the problem.

Is there a straightforward way to get Domo to use a fixed x-axis for hour, date, etc? The workaround above is fairly unattractive, and our situation seems like it ought to be commonplace. I'm hoping that I've missed the obvious! Please let me know. It's no problem for us to pre-calculate and include hour of day, day of week, day of month, etc., if that helps.

 

Thanks

jaeW_at_Onyx

Sorry.  AFAIK there is no way to force Domo to fill in blank rows.  But think about it.  Visualizations only work based on data the know about.  Conceptually we know that we're talking about 'hours in a day' of which there are 24, but Charts usually have no built-in logic to understand that UNLESS you use a date based chart type.

 

SOLUTION: you have to create a 'zero row' for each combination.

I show you how to do it in ETL here:

https://www.youtube.com/watch?v=Xb4QgKYgaqg&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn

 

But you could also probably build this using the new DataSet View Demo (ask your CSM for details) if you can figure out how to do a CROSS APPLY using a VIEW built on two other VIEWs.

 

Hope that helps!

DataSquirrel

Thanks for the answer, much appreciated. Better than having me spin my wheels on this again. I watched your video, and recommend it to anyone who is interested in this topic. Nice use of your time in lockdown!

 

Not to be a jerk about it, but It seems entirely reasonable to me that Domo could provide a structured x-axis for a number line or time line. I came to Domo after a year with my head in D3, where building an axis can be, and usually is, independent of the data set. When preparing the chart, the x-axis is built out using a definition, like 0-23 and then the data is plotted against that. You just need to match a column (raw or calculated) like hour_number against the x-axis. This is also natural and helpful when you're plotting against a time line or number line that extends past now to the right. Like, if you want a days-of-the-month chart that has each day of the month, regardless of the current data.

 

I can synthesize the rows in Postgres, if I have to. (I do this now, in more limited circumstances) I hadn't considered the ETL tools in Domo. I've only ever tried them out and, they're the first and only visual programming tool I've ever thought seemed really cool. I'm helping out a colleague, and I think he would really like what you showed in your video, so I'll pass all of this along to him.

Thanks again for the answer.

 

P.S. Since you obviously are fluent in SQL, a couple of details about Postgres that you may or may not be aware of. The DISTINCT ON behavior is very nice as you can pick which value from your grouped data to pull out for associated columns. So, maybe you wouldn't hate DISTINCT with the same firey passion that you do now, at least in some cases ? And, the generate_series() function, not standard SQL, is amazing. You can synthesize a number or time line in a CTE, and then LEFT/RIGHT JOIN against that to get the 0/NULL rows you want.

 

jaeW_at_Onyx

@DataSquirrel 

Thanks for sharing!

 

Re: structured axis.  In SQL world, I know that is attainable in MDX and OLAP cubes; however, I'm reasonably confident Domo achieves its speed by avoiding processing 'too much' data at the client level.  Analyzer is constructing a SQL query which is then executed server-side and then laid into the viz.  If you wanted to define a fixe set of axis members, you'd have to calculate each result per cell with the response from the SQL query ... which i suppose is do-able, but defo not in base Domo ATM.  

 

I hear you , D3 has a lot of flexibility ... but that flexibility comes at the price complexity getting started.  FWIW, you can build your own custom visualizations and load them to Domo as custom apps.  Domo's Phoenix Library would be the place to start:  https://github.com/DomoApps/domo-phoenix

 

Thanks for the tips about Postgres.  It's always cool to learn about new functions.  After spending a few minutes trolling the internet it's unclear to me if the execution plans are different (re GROUP BY vs DISTINCT ON).  The educator in me prefers GROUP BY b/c you can create an artifact (row_count = sum(1) ) that tells you how many rows were being aggregated.  With new data, I find a lot of people make wrong assumptions about cardinality, so row_count can help validate.

 

FWIW, I don't hate COUNT( DISTINCT ) b/c I believe GROUP BY is better.  My ire is primarily reserved for the visualization layer.  I have clients with multi-billion-row datasets and they expect to do a count distinct on it AND get fast performance without any performance tuning ... it doesn't work.  I always push myself to build datasets that answer the question without requiring COUNT(DISTINCT) 

 

Generate_series() also cool, but again, I push people to use design patterns that can be used in any ETL tools (Magic, MySQL or Redshift). -- A numbers table can be recycled!

 

I do heart me a CTE and wish more tools supported it; however, the target audience / developer in Domo doesn't usually know SQL very well.  I do recommend clients split what would be a CTE or a subquery into a preceding table b/c it makes refactoring easier.  Recently a client came to me head-in-hands b/c their SQL consultant built a query that spanned 83 lines and they were tasked to recycle the code in other places.

 

Heh ... sorry if this sounded super defensive ? I'm not.  it sounds like you're supporting users around you!  Just wanted to share some ideas and paradigms I try to follow to keep people from asking me questions about code I've written for them!

DataSquirrel

Thanks for the follow-up, you don't come across as defensive at all. We're working in different contexts, as you point out. Your points sound well-reasoned, and are well-articulated.

 

The most impressive thing about Domo is probably the level of power it gives to people who don't know traditional programming languages or SQL, but who do have good questions about their data. For this, I don't think Domo has a peer.

 

I just see it differently. A fixed x-axis for time, date, and certain number lines would be useful in a broad range of situations, and is entirely natural-feeling to, I'd guess, anyone. It's not an inherent limitation of plotting, it's something to do with the Domo approach to plotting. Domo's not quite consistent in this way:

 

• Max/min/etc boundaries on many charts are set as hard-coded properties in the Analyzer. I'd, sometimes, prefer dynamic/adaptive settings drawn from the data in view.
• Contrarily, the x-axis for, say, hours of the day, is based on the data present and cannot be set prescriptively in the Analyzer.

 

Domo does have projection-based charts, some curve-fitting features, and perhaps some embedded interpolators under the hood (?) I'd much prefer a defined series with 0s to a ton of generated data. (Either before import, in a processing chain within Domo, or via some imaginary interpolator.)