Bar Chart set to show last 12 months, is only showing 11 months of data as the first value is zero.

Bianca_M · 2021-07-07T00:52:34+00:00

There was an error rendering this rich post.

I have a Bar Chart with the last FY worth of data up to 30 June 2021. I'd like the chart to show the last 12 months of data, however, it is only showing the last 11.

There is no data in July 2020, which is why I'm guessing it's not showing the column. The nature of the data is that there will be no entries if there were no data points, so I can't just add a data point that says 0. I need it to show a zero value for July. Is that possible?

Find more posts tagged with

Analyzer

Date

Card Builder

Accepted answers

All comments

GrantSmith

Hi @Bianca_M

There is an option to fill missing dates on the bar chart however it only fills dates between your first and last date. As such it won't exactly help in your case. You'll need to make sure the July date is included in your dataset. You can utilize the Date dimension data set from the Domo Dimensions connector and left join your dataset to this dataset based on the date and then graph based on the dimension date value and not your dataset value.

For reference here's where the option resides to fill missing dates:

Screen Shot 2021-07-07 at 10.08.52 AM.png

jaeW_at_Onyx

@Bianca_M , @GrantSmith 's recommendation to LEFT JOIN in a Date Dimension is accurate but also a bit more nuanced. If you add in a Date Dimension you'll basically guarantee that you have one row for each date in your dimension table and IF there's matching activity you'd fill in the right side with information about that activity.

But if you build your card and then filter on Region or Salesperson your card will no longer include the 'filler dates -- July 2020' because that row ONLY has date data.

To continue to include that the empty months AND support filtering on other columns, you need to take the approach of saying "i want at least one row per month for each region and salesperson combination" (or whatever your dashboard filters are). then generate that universe of rows.

In SQL you'd do a CROSS APPLY or a CROSS JOIN on Date and SELECT DISTINCT columns of interest.

In Magic you follow this design pattern: https://youtu.be/Xb4QgKYgaqg

Takalla