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

Bianca_M
Bianca_M
edited July 2021 in Connectors

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?

image.png


Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Takalla
    Takalla Member
    edited October 3

Categories