Dashboard chart from Pivot breaks each time it is refreshed
I have a series of dashboard charts that are created directly from a sheet created and updated by the Pivot app and, each time the sheet gets updated based on the schedule defined in Pivot, the dashboard charts break and present the message "Unable to Display Chart." I have seen other post that contained this message for other scenarios but not this one specifically. Here's a sample of the sheet created by Pivot. One dashboard chart is created for Group #1 and charts all of the categories (rows) by status (columns) and a second chart does the same thing for Group #2. The input sheet has the category column as a single-select column and restricted to only the predefined values so I know that no new values are being added to the Pivot.
Any idea as to why the charts are breaking when Pivot refreshes it? Any suitable workaround other than coding all of the formulas manually and abandoning the use of Pivot?
Thank you
Answers
-
Hi @Stu Benoff
I believe this is because the PIVOT app deletes/replaces the rows when it refreshes, versus just updating content in the same rows. This means that when you make the manual row selection from a Sheet for a Chart widget, those rows no longer exist after the Pivot app runs.
The way I would use this data in a Dashboard is to then create a Report off of your source data. The Report will bring in the newly created rows after the Pivot runs, and a Chart Widget uses the entire Report as its source, so you don't need to manually select rows. Does that make sense?
Let me know if this works for you!
Cheers,
Genevieve
-
Hi - thanks for your reply. As you can see the pivot is two-dimensional. Round # over Category. I added an =Parent formula to the sheet created by the Pivot and it seems to remain in place after the sheet gets refreshed so that may work to get the round # into each of the child rows.
So I need a report to extract the data from the source sheet to go into the Pivot, configure the Pivot, and then use another report to preserve the data for the dashboard charts.
Really hard to see the value, that we pay extra for, to use Pivot. I could have created a summary sheet by now.
Stu
