Do not display rows with null/zero values on dashboard

neetuchopra05

Hi,

So, I read a few questions on the community but didn't get answer to my question.

I have a smartsheet with a lot of names on primary column and there are values assigned in different columns in front of these names. Many of these are 0 or null values. I know how to use IF function to have null values instead of 0 values.

But in dashboard, when I display all names, I want only those names to appear that have non-zero values in it. How do I hide names with 0/null values?

How can I do that?

Regards,

Neetu Chopra

bisaacs

Hey @neetuchopra05,

Could you create a Report, then filter the names that don't contain null/0 values, then use the Report in the dashboard?

neetuchopra05

So, I have created the below report. Here, I have mentioned the name of column on Row1 for each employee. Now, I want to display only those employees which have value=1 in the dashboard for the mentioned column.

So, my metric widget named 'Param1 April' in dashboard should only contain:

XYZ 1

ABC 1

I don't want other names to be mentioned here in dashboard. Now, the value keeps changing. And I want dashboard to get updated with names which has value =1. How do I do it?

Kindly assist.

neetuchopra05

Any insights/help would be appreciated!

Paul Newcome

TO use a metrics widget instead of a chart, you would need to use a formula in a cell along the lines of a JOIN/COLLECT to pull in the names and then reference this cell with your metrics widget.

=JOIN(COLLECT([Name Column]:[Name Column], [Number Column]:[Number Column], @cell = 1), CHAR(10))

Make sure to apply wrap text to the cell.

neetuchopra05

Unfortunately, it will still show names of employees on the metric widget for whom values are null. I want to avoid that as well.

Also, is it easier with a chart? If it is, I can go that way as well.

Please advice.

Paul Newcome

Did you try it? the COLLECT function has a filter to only pull in names where there is a 1. That means it should exclude anything that is not a 1.

To do this in a chart, you would create a row report that only pulls in rows that contain a 1 and then use the report for your chart widget.

neetuchopra05

Ok, so that would be my solution. But the data I have is totally different to sample data that I provided above. I have multiple sheets and columns to pull the data from. I can not share the original data here. Is there some way to get on 1-on-1 call to resolve this?

Paul Newcome

If you have multiple sheets, my suggestion would be using a report.

neetuchopra05

Great! But one point I would like to mention, I am not pulling names using the formula. Names are already listed in current sheet. I am pulling data relevant to these names from another sheet. So, when widget is created, names are already there in the rows. Similarly, column names are already there. But I don't want to display all these names unless they have non-zero values.

neetuchopra05

Thank you, Paul. Using Join Collect, I got the resultant names in one cell. But it doesn't solve the issue. I need these names in 4 different cells so that I can get their values in front of them as well. How to do that? I am new to all this.

Paul Newcome

You mentioned before the value would be a 1, so if you need the 1 in front of the names, you can incorporate that like so:

="1 " + JOIN(COLLECT([Name Column]:[Name Column], [Number Column]:[Number Column], @cell = 1), CHAR(10) + "1 ")

neetuchopra05

Oh, my bad! For some cases, it can be more than 1 as well. Would report be a better solution here?