Issues with updates to multiple summary tables

[HCurren]

I've been trying to configure Grist to analyze numerous Sums and Averages for a running total record for all of a single type of report ever filed in my filing cabinet. The first summary table I made referencing that main record updates without error and runs flawlessly calculating average times to complete various job types. However, the other summary tables I have made to group the data in different ways to run formulas against have run into issues when I try to reference them with my chart widgets; they either aren't listed for reference at all, or when you select them the data will be labeled with only "A" "B" or "C" instead of the column names I've assigned. I got one to work by detatching it from the main record, and then re-selecting the main table as it's data, and while it pulls updates from the main table now it won't push them to the widgets. Is there a built in limit to how many summary tables I can reference with widgets?

[dsagal]

Hi @HCurren. There is no special limit on summary tables.

One guess is that your problems are due to the confusing interaction between summary tables and charts.

When creating a chart, I suggest first creating a table widget with the data summarized and grouped in a suitable way to be charted. In such a table widget, it's easier to see what's going on. Once satisfied, you can change the widget to "Chart", and then configure the X- and Y-series, and other chart aspects.

For example, in this Investment Research template, the bottom-right chart shows totals of investments by month for the selected category. If you click "Change Widget":

you'll see that its data source is a summary, grouped by both Company_category_code and funded_year:

It's linked to another widget that lists Company_category_code. This means that for the selected category, the data contains a bunch of summary rows, one per funded_year. This allows the chart to be configured to show funded_year as the X-axis, and raised_amount_usd as the data series:

If your issue is that there are certain series (Y-values) that you want to see, but they aren't listed when you click "Add Series", you can switch the widget type to Table using the "Change Widget" button. In a Table view, you can add a new column with the formula you need (e.g. AVERAGE($group.SomeValue)). When you create a summary table, such columns are created automatically for any numeric columns (with SUM as the formula), but if you have columns that aren't of type "Numeric" or add columns after creating the summary table, then you need to create the calculated columns manually.

If this doesn't help, maybe you could put together an example that you can share (make publicly viewable), so that we can understand the problem better.

[HCurren]

I have created chart widgets on visualiztion pages that work, the problem is that they don't seem to update when the data on the underlying summary tables on "back-end" pages update. an example of my current structure;

• Time & Expense DB (complete record of every T&E Report ever filed and the data on it) -Lead Averages Back-End (Page with summary table linking to Time & Expense DB, calculating time averages per job and per step) -Vehicle Averages Back-End (Page with summary table linking to Time & Expense DB, calculating cost averages per vehicle) -Hotels Back-End (Page with summary table linking to Time & Expense DB calculating time average difference between jobs we stayed at hotels at and jobs we didn't)

-Visualizations page (Shows some average visualizations such as average time per job-type, and time with/without hotels per job type) -Lead Visualizations (Visualizations of average time per job type selected by Lead name, linked to 'Lead Averages Back-End') -Vehicle visualizations (Visualizations of Cost per Mile, Cost per hour, etc per vehicle, linked to 'Vehicle Averages Back-End')

All of these tables and references work initially. The problem is when we add new data to the Time and Expense DB, the records in the back-end tables update, but the visualization pages stay unaffected. For instance I put a test job in that took 100 hours. My average for that job type on the Back-End summary table changed from 12.08hrs to 25hrs, but the visualization for that table still showed me at 12.08hrs. This can also be observed when adding a new lead altogether, they will show up on the back-end tables, but you can't seem to visualize them with the existing charts.

I can seem to fix this by unlinking the back-end table from the DB, and then relinking it to the DB. This seems to update the charts on the visualization pages with the new data, but won't work for any future data until the same process is repeated.

There seems to be a limit to the levels of references or links between pages and widgets maybe?

[HCurren]

Oh wow, Okay so I've figured out a better fix than delinking and relinking. If I select the data for the chart widget as the Time & Expense DB and group it by the same columns that I grouped the related Back-End table, then the calculations I made on the back-end tables start showing up for me to select as series, even though it does not show up anywhere (hidden or not) on the Time & Expense DB page. That is confusing!

[dsagal]

Hi! Any chance you could share publicly a copy of the document without sensitive data? You could use "Duplicate Document" to copy the document without the data, and add some made-up data.

The reason I ask is that the interaction between charts and summary tables is indeed confusing, and we are doing some work to make it easier. It would really help to understand your experience, both ways you tried (the one that didn't work, and the one that sort of works but is confusing).

[HCurren]

Alright, sorry for the delay but I finally got around to sanitizing a copy of our database to share with you.

MDB Copy for Public Review.zip

Please let me know if you have any further questions. if you check all the formulas you may even see where I had to manually average things because averaging appeared to be broken on some tables (HD Back-End I believe was one.) Quite a few glitches and work around on this file.

edit: although glitch may be interchangeable with user-error for all I know.

[dsagal]

Hi @HCurren,

Firstly, thanks for sharing the sanitized copy. It's very helpful. I have a few notes that may help you now, and some for our team for things we should improve.

• You mention an issue with averages in summary tables. E.g. the default formula for Check_Out_Time in a summary table is SUM($group.Check_Out_Time). You can just change SUM to AVERAGE, and AVERAGE($group.Check_Out_Time) would be correct. I see that you use that in a number of tables. If you keep the original column as SUM, you can add another column of the form $Check_Out_Time / $count, that would also get you the average (sum divided by count).

• I explored the Lead Visualizations page. The chart should update noticeably when I enter, for instance, a very large number into Labor Total column of Time & Expense Database page. But it doesn't always seem to. The reason, I see now, is the very confusing linking. The chart in Lead Visualizations is linked to the top left widget which summarizes the Time & Expense Database by Lead. There are 3 leads in that widget. In the sanitized data they are indistinguishable because the Technician Directory is missing names (so they all show up as Blank). But if you do select different leads, the linked chart does update. In other words, it shows the summary of average times by Job Type for the selected Lead. Which may be what you intended. So perhaps no issue there?

• I see a bigger issue in the Assistant Visualizations page. It's linked to a Technicians Directory widget, and it updates when I change the selection there, but it's not quite right. The way to make it work currently is to replace the Technicians Directory widget with one that summarizes Time & Expense Database by Assistant; and change the bottom chart widget to one that summarizes Time & Expense Database by Assistant and Date. Then they would link correctly AND update correctly. This is a case when it's important that data in the chart is summarized appropriately.

• As you already discovered, the only way to get a Chart to show some other calculation than SUM is to first edit a formula in a table view of the data that's summarized in the same way. One way to do it is to switch between Chart widget and Table widget (without changing data or group-by selections, as I suggested in the first response).

For our end, this confirms some issues that we are aware of, and have started planning solutions for:

1. The bug reported here https://github.com/gristlabs/grist-core/issues/175 is real: it's easy to lose linking when changing how data is summarized. A workaround is to unlink and re-link, but we need to fix it.
2. It should be possible to link summarized data that groups by a Reference column to the referenced table. E.g. in the last example above, a chart summarizing Time & Expense Database by Assistant should absolutely be linkable to a widget listing Assistants. Currently that doesn't work. We should fix.
3. Charts almost always require summarized data. We have some plans in the works to automatically summarize by the columns that make sense (such as X-axis, and whatever column is used for linking). This should make charts much more usable. (Related to https://github.com/gristlabs/grist-core/issues/81.)
4. It would be good to come up with some way to see what formulas are used for what series in a chart, and to be able to edit them, as well as to add new series. The current way (switching widget between Chart and Table, in which series can be edited as columns) is both awkward and hard to discover.