Line Chart- Missing Values Show Gap Instead of 0; Bonus: how to deal with many lines

Kutsch
Kutsch Member
in Cards, Dashboards, Stories

I'm creating a line chart to show employee terminations over time.

In some instances, there is no data/terminations for a given period of time.

The chart is showing gaps when there is no data present. I'd like it to plot 0 for those years instead of a gap.

I thought selecting 'hide empty values' would do the trick, but I didn't see a difference. Here's an example of what I'm dealing with:

image.png

My next step is to figure out a better way to visualize all these lines without the clutter… so any recommendations on that would also be appreciated lol

I was thinking of maybe making 3 separate plots with about 4 lines on each. But that makes it harder to compare

Tagged:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    There is a free calendar dataset available for download in the Domo Dimensions connector, you can search for it in the Domo appstore. It will allow you to create 0 values for days, weeks, months, quarters, or years so that when you plot the line chart there will be 0 values to show on the card. The steps are:
    1. Create a new ETL with your data and the calendar data as inputs
    2. Aggregate your data and left join it to the calendar on Date = termination date
    3. Use a formula tile to populate a 0 for any dates with a null value.

    Another option is to just use an excel file or Domo webform to create 0 values for where you are missing data and then append it to your dataset.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 2023

    To plot a 0 for gaps you will have to do some ETL work. You'll need to use a calendar dataset and populate it with your values but 0's for the years where applicable. This way Domo will have a value to read for 2019 (for example) which will be 0.

    For how to solve the many lines issue, is there a logical grouping for the lines? Department for example? If so you could use a quickfilter to toggle between those groups so less lines are on the chart at once. But if you need to truly compare the trends of all these values at once I would say that the number of lines you have aren't necessarily too many. My own opinion is that it is rare that multiple trend lines really need to be compared all at once.

    If I solved your problem, please select "yes" above

  • Kutsch
    Kutsch Member

    @ColemenWilson I figured I might need to utilize beast mode or ETL, but was hoping there'd be a quick fix- like a box to check- before I went down that path.

    Yup, each line refers to a department. I have dropdown filters at the top of my dashboard, so the user can easily filter the department.

    Appreciate the insight :)

  • ColemenWilson
    ColemenWilson Coach

    What is odd is that usually checking that box will at least bridge the gap so there is no space. It won't plot a 0, but it will remove the gap.

    If I solved your problem, please select "yes" above

  • Kutsch
    Kutsch Member

    @ColemenWilson What did you mean by using a calendar dataset?

    More context:

    In these plots, I was using a COUNT of the employee id.

    The dataset I've been using has employee id, employee type, termination date, department, and some other info.

    If I create a card from a different dataset, I'm assuming the dropdown filters I created will no longer apply to the new plots

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    There is a free calendar dataset available for download in the Domo Dimensions connector, you can search for it in the Domo appstore. It will allow you to create 0 values for days, weeks, months, quarters, or years so that when you plot the line chart there will be 0 values to show on the card. The steps are:
    1. Create a new ETL with your data and the calendar data as inputs
    2. Aggregate your data and left join it to the calendar on Date = termination date
    3. Use a formula tile to populate a 0 for any dates with a null value.

    Another option is to just use an excel file or Domo webform to create 0 values for where you are missing data and then append it to your dataset.

    If I solved your problem, please select "yes" above

  • Kutsch
    Kutsch Member

    @ColemenWilson I think your explanation would get us to the right solution, but there's a bit of work that will be need to be done to properly execute.

    The problem is that there is no data/rows for some of those criteria (year/department). So I think we'd have to manually input department data for those particular instances (and the other variables we're plotting over time).

    Due to time constraints, we plan on explaining that the gaps are values of 0.

    Thanks for the help!

Categories