Display all column data even if blank or null

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.

Find more posts tagged with

Accepted answers

KurtF

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

All comments

Apollo

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,

KurtF

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?

Date	Person	Qty
6/21/17	Joe	5
6/21/17	Jill
6/21/17	Bernice	1

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

DavidM1

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

CASE WHEN qty IS NULL THEN 0 ELSE qty END

DavidM1

Ok thank you for the help!

user07459

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