Dashboard Charting & Data Aggregation

Joseph McGlawn

I am beginning to explore the dashboard functionality of SS and trying to get a better grasp of aggregating data in various ways in Sheets. I want to ensure I understand how the data must be organized in order to use charts and other visualizations before I get too far along.

Data must first be aggregated up to the point of rendering the visualization and cannot be displayed across Sheets.

Assuming that's true, how do I best setup an aggregation sheet to take care of this? I've reviewed several templates and other community discussion on related topics, but most of these seem to involve static elements of aggregation. For example, charting Total # of Tickets per User involves a data component such as User which can be dynamic. Do I have to manually insert a row each time a new data value populates or can this be automatically inserted?

Thanks in advance.

Paul Newcome

I'm not sure I follow.

I don't know what you mean by "Data must first be aggregated up to the point of rendering the visualization and cannot be displayed across Sheets".

Additionally, to use your example of charting Total # of Tickets per User...

You would basically set up a table that lists out all users down a column. Then you would use formulas such as a COUNTIFS to generate the total. The formulas (depending on your setup) would update the total counts as they changed providing live data to your table.

This table would be used for your Chart widget on your dashboard so that a visual representation of the live data is available.

The data is not always static (unless you want it to be) but honestly pulling static data can sometimes be harder than pulling live data in Smartsheet.

Joseph McGlawn

"Data must first be aggregated up to the point of rendering the visualization"

I have to create additional tables first before a chart knows how to display it. Ie it can't natively aggregate, I have to do it first?

"and cannot be displayed across Sheets"

I can't display data from multiple sheets in one chart, it maps one-to-one to a sheet?

So take the below table as an example:

Whether it be a bar chart or pie/donut chart, in order to display something that shows User A 3, User B 2, User C 1 (number of line items counting user), I have to make an entirely separate report to aggregate this count first? Furthermore, what happens when additional Users populate in this report and/or a User falls off the report. Can I make the aggregation table dynamic to adjust with the data or is it static?

Hope this is more clear.

Paul Newcome

You are correct in that you need to pull the data you want displayed. Creating a table with counts (such as your above screenshot) if you wanted to show that data in a chart.

You can reference multiple sheets using cell linking and/or cross sheet formulas so that all data across multiple sheets is pulled into one data set if that is what you need.

In reference to the final portion of your above post...

There are a number of ways these data points can be pulled and calculated. You could have a list that is manually updated, or (depending on sheet structure and formulas and whatnot) it could be possible to create a dynamic list.

So far, everything that you have wanted to display can be pulled from multiple sheets and can be built to be dynamic, BUT you do have to pull those data points together for Chart widgets.

Joseph McGlawn

Ok. How do you make a list dynamic? Keeping in mind this is only one column I've referenced and there would be many other's I'd be looking to aggregate stats on as well from the same or multiple data sources.

Paul Newcome

It depends on the data, the data type, the layout in the source sheet(s), your intended display/end result...

The basic idea is that you use a JOIN/COLLECT/DISTINCT combo to pull one of each different entry (names for example). Then you would parse that out going down a column or across a row depending on your final needs.

That would give you your dynamic listing for display and for reference in your other formulas.

The best solution though really does depend on exactly how you want the data to be displayed in the end result.

Joseph McGlawn

Going back to the example table as reference if I'm reporting on User and Status, how do you create a single report that dynamically adds and removes rows for both? I envision you have a single column that has the distinct values for both of those columns a second column for example that counts the number of records with those values and a third that sums points for each of those values. See below as an aggregated example of the above.

Paul Newcome

I can confirm that you are correct about the first two columns. One is a list of distinct values. The other is the formula for the count.

I am not sure What you mean regarding the Points column or how you calculate it, but if you wanted to pull/calculate that data, it can be done with formulas I am sure.

To create the first column of distinct values, you will first need to join all of those distinct values with a unique delimiter. Then a series of formulas can be used to parse that string out down the column. I will look through my notes and other posts and get back to you about this portion. I recently helped someone else in the Community with a similar solution.

Joseph McGlawn

Points column is just one of the columns in the dataset as an example. Just something else in the data that can be aggregated for metrics.

Ok. Looking forward to it. Just trying to understand how to minimize manual intervention when aggregating information. It just doesn't appear as obvious to me as it does for example in Google Sheets or Excel when doing charts.

Paul Newcome

Understandable. The nice thing is though that once it is set up, you can pretty much ignore it unless you need to make an actual change. The data will be live once everything is put together.

To pull a list of distinct entries from another sheet and parse it down a single column, you would want to set it up similar to THIS SHEET. My source sheet has a bunch of random numbers (I was using it for something else), but this will also work with any other data. Just make sure the delimiter you use is NOT something that can be found in the original data. In my example I used a comma delimiter, but you can replace that with anything you want so long as it is unique from the data.

For each of the formulas in the example, once you get to the point where you are dragfilling, you will need to dragfill down to account for the maximum number of unique data points. So if you think that you should have no more than 25, go ahead and dragfill down to 50 or something.

If you cover 50 rows but only have 10 unique entries, then the remaining 40 rows will be blank and will not populate the chart on your dashboard. It won't even leave a huge blank space or anything, but once you have an 11th unique entry, everything including the chart will adjust accordingly. You just need to make sure you include all of those extra blank rows in your chart range so that they will be picked up if they are automatically filled in.

Take a look at everything and let me know what you think about getting your list set up.

Joseph McGlawn

The dragfill is a dealbreaker. I appreciate the help in trying to come up with the best solution possible, but the maintenance to do simple aggregation seems unnecessary and I need a zero-maintenance sort of solution. It seems as though the only way to aggregate data in a pivot like manner is the Pivot App? And that's more money...

Paul Newcome

The dragfill would only have to be done once. After you are set up, it should require zero maintenance.

I have no experience with the Pivot App, so I wouldn't be able to provide any insight into it.

Joseph McGlawn

The dragfill would have to be extended further to the extent you have unique items show up in your dataset that extend beyond how far you drag down no? If I dragged down 50 rows and I get the 51st unique data item, then it would be missing? Or will Smartsheet automatically extend the formulas down?

Paul Newcome

If you drag down 50 and end up with 51, then the 51st would not show. That is the reason for extending well beyond the anticipated. Do you anticipate 75? Then assume your guess is under and call it 100. Then dragfill to cover 150. Dragfilling is quick and can be done across multiple columns at the same time. Anticipate 500? Dragfill down to 750. The blank rows won't show up on the dashboard, so the only limit you have is the 5,000 row limit on a sheet.

Paul Newcome

If you are concerned that you didn't account for enough unique entries, then dragfill some more. Or you could use a basic counter to see how many unique entries there are and compare that to how many rows you dragfilled and have an alert automatically sent to you when the number of unique entries gets within a certain range of how many rows you prepped for.

Joseph McGlawn

Think this gives me enough info. Thanks.