Omit records with No data on Charts

I have a Workspace with a form that feeds into a sheet Then I have a calculations sheet that adds up everything (most of which us SUMIF). Now a few of these Workstreams (which are in a dropdown list on my form/main sheet do NOT have any data currently associated with them. But that will change. And like I said these Workstreams are part of a dropdown, AND they are also part of Automated Workflows that fill out different parts of the main sheet (and send notifications) based on the Workstreams. So although a Workstream does not contain data yet, it is essential that it is listed.

My problem, I do NOT want the Workstream to display on my charts until there is data, Right now they display with 0. I don't want them to display until there is data to report, I want them to be omitted until that time. I tried a few things, but always came up with an error, like Unparsable or something. Since this is not only the initial totals, but also calculations based on those total, I am having more difficulty.

An example of a formula I currently use on this sheet is:

=SUMIF({Workstreams}, Workstream@row, {QMS Entity Documents Before})

then a calculation based on that collected data:

=([QMS Entity Documents Before]@row - [QMS Entity Documents After]@row) * [QMS Annual Maintenance Burden]@row

Find more posts tagged with

Workspaces

Sheets

Formulas

Accepted answers

All comments

Samuel Mueller

@Sherry Fox try using a report to chart your data instead. In the report use your same sheet as your source sheet, bring in the columns you want to chart, and filter for that workstream column to "IS Not Blank". Then as workstreams are added they will be included in the report, and in your chart.

Sherry Fox

@Samuel Mueller,

That kind of worked. Perhaps you can help me fine tune the answer. In this cased I have the following 3 formulas. The "burden" is a data entry cell stating 20. Previously I listed the first and third formulas:

=SUMIF({Workstreams}, Workstream@row, {QMS Entity Documents Before})

=SUMIF({Workstreams}, Workstream@row, {QMS Entity Documents After})

=([QMS Entity Documents Before]@row - [QMS Entity Documents After]@row) * [QMS Annual Maintenance Burden]@row

Now when I do the filter this is where the problem coms in. In the first Workstream the numbers from the formulas are as follows:

Before: 16

After: 0

Burden: 20

Hours Saved: 320

But when I did the filter, this Workstream disappeared because I am guessing of the 0 in the After. I need it so that if there is a value in Before OR After the Workstream will remain, however if BOTH are blank, it will disappear. I hope that makes sense. Thanks so much.

Samuel Mueller

@Sherry Fox try this for the report filter

after column is not equal to 0

before column is not equal to 0

IF that doesn't work can you send a screenshot?

Sherry Fox

@Samuel Mueller ,

I actually figured it out on my own before I realized you responded. I realized that there must be 2 conditions set up. That way if the before or after contains a 0 and the other contains a value it will still display. This is how I set it up, just in case it can be of help to someone else. Thanks for all your help!!!!

Samuel Mueller

Glad you got it working!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.