Charts built off of reports

Leslie Merlino

I have some charts that are built off of reports pulling from multiple metric sheets counting data entered in the last 30 days, 5 days and 24 hours. The reports are set to look at and add only the items that have a number greater than 0 in the 30 days column as there is a huge list and we wanted to simplify that list to just the items that have numbers to report. The issue we are running into is that when there is no data to report, the chart breaks instead of showing 0 values. Is there a way that I am not thinking of to keep the chart from breaking, but also keep that list simplified to just the items that have a number to report? Screenshot attached of the message showing on our dashboard.

Paul Newcome

You could build off of your checkbox idea like this...

Enter a formula into every working row that will automatically check the box for rows that meet the report criteria. So if it is everything that was created iin the last 30 days you could use something like this:

=IF(DATEONLY(Created@row) >= TODAY(-30), 1)

Then in the "helper row" just check the box manually so it never becomes unchecked.

Paul Newcome

You could add a "helper row" to each of the sheets that will always be included in each of your reports. This way the reports are never technically empty.

Leslie Merlino

I did think about that, but I would have to have a number in the greater than 30 days column in order for it to flow to the report, which I think would throw the teams off. Unless there is another way to set the report..... Thinking as I type here...... Would a checkbox that checks if there is a number in any of my 3 reference columns and check the box if there is a value, leave it unchecked if not ( or vice versa). I still don't think that would work as it would still be the same issue, it would either return all rows or none. I kinda feel like I'm overthinking the issue.

Paul Newcome

You could build off of your checkbox idea like this...

Enter a formula into every working row that will automatically check the box for rows that meet the report criteria. So if it is everything that was created iin the last 30 days you could use something like this:

=IF(DATEONLY(Created@row) >= TODAY(-30), 1)

Then in the "helper row" just check the box manually so it never becomes unchecked.

Leslie Merlino

YESSS!!!! So simple too!!! I had thought about something similar, but it just did not click!! Thank you, I believe that will work. Sometimes you just need a fresh perspective. 😉

Leslie Merlino

@Paul Newcome I expanded on your formula and used this:

=IF(COUNTIFS([Last 30 Days]$2:[Last 30 Days]$22, >0) > 0, 0, 1)

This way, if there is a number to report, the "No data to report" row will not show!! Very helpful, thank you!! ☺️ Sorry it did not click right away!!

Paul Newcome

Good thinking on how to remove the "No data to report" row when there is data. Nicely done! 👍️