Adding in missing dates for charts

Say you're charting a metric per day (i.e. y-axis: sum(foo), x-axis: date). When displaying bar charts for this metric, there are often no entries for things like weekends.  If there's no entry in the table for a certain day, then Mode's Chart doesn't show that x-axis entry.

Is there a way to graph days with 0 for the metric?  Either through an option while charting (i.e. "show all days even with no metrics") or via the table data.  Via table data I've researched solutions that call for selecting from a calendar table or something similar (i.e. "select CURRENT_DATE - X as Day, 0 as Metric") and joining that table with the results table.  However as far as I can tell, Mode has no calendar data to select from.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • We created a dates tables that we left join everything to in order to achieve this. There isn't another way that I know of. 

  • While a pre-existing calendar table is a common option, it is possible to generate one on the fly with some tricky SQL using row numbering and date math.  My favorite option is to create a SQL table value function with start and end dates as parameters and left joining to the function results in a CTE.

  • In fact, that's how we coped with this: we created an SQL table value function. We used 2 dates for parameters (start and end ones) and we joined to the function results.

Like Follow
  • 9 mths agoLast active
  • 3Replies
  • 171Views
  • 4 Following