Unable to build a chart widget

Jeffrey Domanski

I'm getting a tad frustrated and I am hoping someone can help. I am trying to create a fairly simple chart for capacity planning that shows the amount of planned travel days for our team arranged by person. (To help us keep things balanced). I am unable to import any sheet or report. They get rejected every time and I can't locate any literature or resources that explain "why" this is happening. I'm sure it is logical - I just need that help to understand the rules of what has to be in a given sheet or report for it to qualify for a widget chart. I'd be happy to send pics or discuss virtually if anyone out there knows the quick solution. Thank you

Genevieve P.

Hi @Jeffrey Domanski

Yes, you will want to create a "helper" sheet with SUMIF formulas in order to calculate this data.

Reports are great for showing that number directly, and you can put this as a Report Widget on your Dashboard with the summary rows collapsed like so:

However Chart Widgets cannot currently use Report summary rows as the source for the chart. Our Product team is working on this feature, but it's not released yet. You should see a small disclaimer when selecting a Report as your source:

Let me know if you need help creating your formulas/numbers in that helper sheet!

Cheers,

Genevieve

Genevieve P.

Hi @Jeffrey Domanski

Can you explain what you mean by "rejected"?

Chart widgets need numerical data in order to map columns/graphs.

For example, if you have 5 rows with content, 3 with a status of "Complete" and 2 with a status of "In Progress", a Chart widget won't automatically be able to create calculations and identify the number of rows for each status. These rows only contain text, but the chart needs numbers.

Ex:

Instead, you'll need a helper metric sheet with formulas to create those numbers (counting each status and returning a value, generally with a COUNTIF or SUMIF cross-sheet formula).

Then once you have a grid that houses numerical values, you can map this into a chart. Here's how a helper sheet might look:

So in your instance, it sounds like you need to create a helper sheet which SUMS the number of Travel Days IF it's associated with a specific person.

Do you have this already set up to look like my second example above? If not, I'd be happy to help you create the formulas needed and it would be useful to see a screen capture of your source sheet (but please block out sensitive data, or use dummy data instead).

Here's some information on Data Formatting for Charts: Compare Data with a Chart

You may also find this Webinar on creating Charts helpful (see about 18 min in): SmartStart: Dashboards

Cheers,

Genevieve

Jeffrey Domanski

I have a sheet that tracks travel for our team (travel being scheduled visits to customer sites). I want to create a widget from a report that summarizes each of their total currently scheduled number of days. The report doesn't seem to want to aggregate their travel instances and so I was using the GROUP function but I don't think the widget liked that. I can create the widget from the report but again doesn't combine each person's total travel days. I'm wondering if I need to add more rows and do a SUM IFS function that does the aggregating for me first?

Genevieve P.

Hi @Jeffrey Domanski

Yes, you will want to create a "helper" sheet with SUMIF formulas in order to calculate this data.

Reports are great for showing that number directly, and you can put this as a Report Widget on your Dashboard with the summary rows collapsed like so:

However Chart Widgets cannot currently use Report summary rows as the source for the chart. Our Product team is working on this feature, but it's not released yet. You should see a small disclaimer when selecting a Report as your source:

Let me know if you need help creating your formulas/numbers in that helper sheet!

Cheers,

Genevieve

Genevieve P.

Hi @Jeffrey Domanski

You can now create Chart Widgets from the top Summary rows from a Grouped Report! See the Release Note about this new feature here: Charting using Grouping and Summary

Cheers,

Genevieve

Tapio Pulkkinen

Hello @Genevieve P.

That new feature is really good and I waited it for a long time but...

Is there option to choose if I want or don't want the chart to be created from grouped information? For example, I have dashboards where I need to display my projects as a grouped information and as a chart. And that the chart would need to show every project as one. 

I can get around this by having two similar reports (one grouped and one not) but it would be easier and simpler to have just one per needed information.

Best regards,

Tapio Pulkkinen

Genevieve P.

Hi @Tapio Pulkkinen

That's a great question! You are correct, currently the way to do this would be to have a duplicate Report - one with Grouping and one with just the source rows. There isn't a way from within the Chart Widget to select if you're wanting to map the source rows or the grouped summary.

This is a new feature that I'm sure the Product team would love to hear your feedback about! If you have a moment, please fill out this form here with your suggestion.

Cheers,

Genevieve