Display all column data even if blank or null

DavidM1
DavidM1 Member
in Charting

I am working on a horizontal bar graph with a list of names in the y axis. The x axis shows a qty of how many times the person has completed a certain task. If for example I filter by week, it will only show me the names of people who have an associated quantity for that week. Is there a way to force a 0 into a blank row or add a row somehow so that all names are displayed. If there was no qty associated with that person for the week for example then I want it to show 0.

Best Answer

  • KurtF
    KurtF Domo Employee
    Answer ✓

    That's how I would approach it. You would do an outer join of the person list with your dataset that has your actual results. Depending on how you approach that you may have some persons in the final dataset with NULL values. You can always create a BeastMode to set the value to zero if there is a NULL there.

     

    CASE WHEN qty IS NULL THEN 0 ELSE qty END

    KurtF
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • Apollo
    Apollo Domo Employee

    Hi David M1,

    In the chart properies > data label settings, there should be a check box for "Show when Zero". If this check box is selected the data should show even if it's zero. 

    Thank you,

    I work for Domo
  • KurtF
    KurtF Domo Employee

    What does your underlying data look like? Is there a row with a person's name and a NULL value for that person for a given week?

     

    Something like this?

     

    DatePersonQty
    6/21/17Joe5
    6/21/17Jill 
    6/21/17Bernice1

     

    Or is there just not a row for the person during the week you want their name to display a zero for?

    KurtF
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DavidM1
    DavidM1 Member

    My problem is that there is no row for that person on a given date. As a work around, I created a seperate dataset with everyone's name in, listing a 0 in the Qty for all dates and then joined the original and new datasets together in a dataflow.

     

    Maybe that is the best way to achieve it without significantly manipulating the original dataset?

  • KurtF
    KurtF Domo Employee
    Answer ✓

    That's how I would approach it. You would do an outer join of the person list with your dataset that has your actual results. Depending on how you approach that you may have some persons in the final dataset with NULL values. You can always create a BeastMode to set the value to zero if there is a NULL there.

     

    CASE WHEN qty IS NULL THEN 0 ELSE qty END

    KurtF
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DavidM1
    DavidM1 Member

    Ok thank you for the help!

  • user07459
    user07459 Member

    I actually had the same problem and solved it without fieltering the data in the card, but doing a specific calc field. This avoids manipulating the dataset

    sum(case when `dimension_name`='x' then `y` else 0 end)

     

    hope this helps

This discussion has been closed.

Categories