[Issue] Summary table changes what shown in Chart based on Table

Ask for Help
#1

Hi,

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
    image
  • Then I created a summary table with custom summary formulas with IF inside (i wanna only show this month summary)
    image
  • Then I saw that in chart page, only data from summary table is shown instead of full data from invoices table
    image
    image1280Ă—816 17.5 KB

Why my chart shows data based on summary table instead of normal table?

#2

Hi! I think it’s a combination of two reasons:

  1. 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.

  2. 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.

#3

Thanks for replying.

Ad 1.
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:
image

Ad 2.
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).

Edit 1:
Building chart on other column is giving same issue.

#4

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:

  1. Revert the summary formula for successFee in INVOICES [by clientKey] to the default formula SUM($group.successFee).
  2. 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)).
#5

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.

1 Like