I found a strange behavior about summary tables changing data in charts.
Let me show you:
- I have a table with invoices
- I created a chart showing summaryFee by yearMonth like this
- Then I created a summary table with custom summary formulas with IF inside (i wanna only show this month summary)
- Then I saw that in chart page, only data from summary table is shown instead of full data from invoices table
Why my chart shows data based on summary table instead of normal table?
Hi! I think it’s a combination of two reasons:
Charts naturally use summarized data. For instance, in your example, the charted points are the pairs
(yearMonth, summaryFee). The original table may have many points with the same
yearMonth value, but the chart needs a single number to plot. Summary tables are a natural way to compute that number. You can see which data goes into a chart by using the Show Raw Data option in the widget menu for the chart.
Different summaries of the same table share the formulas for same-named columns. This is why changing the formula for
summaryFee in the
INVOICES [by yearMonth] summary table affects also the formula for
summaryFee in the
INVOICES [by clientKey] table. Sometimes this is great, because the column is consistent (e.g. always
SUM in every summary, or always
AVERAGE in every summary). Sometimes that’s unexpected, as in your case. It’s made worse by some inconsistencies in how these formulas are synced across summary tables; I am not sure if that’s affecting you as well. (Those are something we’d definitely like to improve on.)
One way to side-step these issues is to avoid modifying the auto-generated summary formula. Normally, when summarizing a table with a numerical column
X, Grist adds a column
X to the summary table with the formula
SUM($group.X). This behavior is relied on by charts, in particular. When you need a different formula, the suggestion is: instead of modifying such an auto-generated column, you can add a differently-named column with the formula you want.
Thanks for replying.
Raw Data is showing that chart (INVOICES [by yearMonth]) is based on INVOICES [by clientKey] instead of default table INVOICES. I know that because it has formulas from there where I want to show only value from not yet sent invoices. Screenshot:
In my case, I need to use custom formulas (like in screen above) because I’m using the card widget to show invoices I need to send. (For example, 1 client has 4 invoices, so I sum them into 1 invoice).
Building chart on other column is giving same issue.
What I meant is that if your chart should use the column
successFee which should add up all invoices (not only the unsent ones), then:
- Revert the summary formula for
INVOICES [by clientKey] to the default formula
- In the
INVOICES [by clientKey] summary table, add a new column to that table, with a different name (e.g. “invoicesToSend”) with the modified formula (i.e.
SUM(r.successFee for r in $group if r.invoiceSent == 0)).
Brilliant, works perfectly.
I think that you should add this aproach and description about how right now summary tables works to the documentation about summary table.
I would like to ask if the solution to this problem will be implemented in the future? (all pivot tables individual from others pivot tables)
As far as the proposed solution I constantly use but now my team needs many pivot charts for many variables. This causes that currently I have to copy all the columns needed and give them different names. My basic table has 22 columns so copying them repeatedly causes the number of columns to swell incredibly…
The problematic case you mentioned earlier was when you needed a custom formula for “invoices to send” which only made sense for a particular pivot table (
INVOICES [by clientKey]), and the formula was
SUM(r.successFee for r in $group if r.invoiceSent == 0).
Are you saying that many columns need different customizations in different instances of pivot tables? When the formula is the same, you shouldn’t need to make copies of columns.
We do want to improve behavior, but haven’t come up with a fully satisfactory approach. In particular, consider the situation where you have a summary table
INVOICES [by clientKey], then edit the configuration to change it to
INVOICES [by clientKey, isPaid] (i.e. adding a second group-by column). Would you expect formulas to stay unchanged (e.g. the customized formula for
successFee)? Further, as long as we allow changing group-by columns, you could change them to
INVOICES [by yearMonth], for which you might have – in another page perhaps – same-named columns with a different formula. In this case, should the formula for
successFee switch to the one present in the other page?
In short, what’s hard to reconcile is the expectation that pivot table columns should stay the same (as much as possible) when group-by columns are changed.
It sounds like in your use case, this might be more of a disadvantage. If you can describe your case in more detail and perhaps share a copy of your document (without sensitive data), that may help come up with a good solution.