Support needed to create a dashboard.

[grostim]

Hi !

I have discovered fava-dashboard a couple of weeks ago, and I love it. Thank you for this great addition to fava and beancount.

I am currently creating my own dashboards panels, and i have been able to reach pretty much all my expectations except for my last graph:

I want to create a stacked area graph showing the variation of my various assets over time.
My issue is that for some of the assets, i have some months without any returned balance as there is no transaction that month.
Trying to stack curve with missing points tends to brake the graph.
Is there a good way to fill the gaps in the dataset in order to get a proper result ?

Here is my (broken) graph and the corresponding code.

image

  - title: Actifs 📈 
    width: 100%
    link: /comptes-famille-gros/balance_sheet/
    queries:
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account ~ '^Actif:Crypto'
        GROUP BY year, month
      name: Crypto
      link: /comptes-famille-gros/balance_sheet/?time={time}
      stack: detail
      areaStyle: {}
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account ~ '^Actif:Bink'
        GROUP BY year, month
      name: PEA+CTO
      link: /comptes-famille-gros/balance_sheet/?time={time}
      stack: detail
      areaStyle: {}
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account ~ '^Actif:(Boursorama:AVTim2|Boursorama:AVAnna1|Linxea)'
        GROUP BY year, month
      name: Assurances Vie
      link: /comptes-famille-gros/balance_sheet/?time={time}
      stack: detail
      areaStyle: {}
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account ~ '^Actif:(Boursorama:CC|Boursorama:AVAnna2|Boursorama:LDD|Boursorama:CEL|Boursorama:LA|BanquePostale:LAAnna)'
        GROUP BY year, month
      name: Monétaire
      link: /comptes-famille-gros/balance_sheet/?time={time}
      stack: detail
      areaStyle: {}
    - bql: |
        SELECT year, month,
        CONVERT(SUM(position), '{{ledger.ccy}}') AS value
        WHERE account ~ '^Actif:Immobilier'
        GROUP BY year, month
      name: Immobilier
      link: /comptes-famille-gros/balance_sheet/?time={time}
      stack: detail
      areaStyle: {} 
    type: echarts
    script: |
      const currencyFormat = new Intl.NumberFormat(undefined, {
        style: "currency",
        currency: ledger.ccy,
        maximumFractionDigits: 0,
      });
      const months = helpers.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      const amounts = {};
      for (let query of panel.queries) {
        amounts[query.name] = {};
        for (let row of query.result) {
          amounts[query.name][`${row.month}/${row.year}`] = row.value[ledger.ccy];
        }
      }

      return {
        tooltip: {
          valueFormatter: currencyFormat.format,
        },
        legend: {
          top: "bottom",
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormat.format,
          },
        },
        series: panel.queries.map((query) => ({
          type: "line",
          name: query.name,
          stack: query.stack,
          areaStyle: query.areaStyle,
          data: months.map((month) => amounts[query.name][month]),
          connectNulls: true,
        })),
        onClick: (event) => {
          const query = panel.queries.find((q) => q.name === event.seriesName);
          if (query) {
            const [month, year] = event.name.split("/");
            const link = query.link.replaceAll("#", "%23").replace("{time}", `${year}-${month.padStart(2, "0")}`);
            window.open(link);
          }
        },
      };

If this is not the right place to get support for your tool, let me know, and i will repost my message accordingly.

[andreasgerstmayr]

Can you try

          data: months.map((month) => amounts[query.name][month] ?? 0),
          connectNulls: false,

?

This gives a valid number for every data point (months without matching transactions), and if the series are stacked, I think it shouldn't disrupt the chart.

[grostim]

Hi Andreas. The graph looks much better, however the displayed information remains wrong.
When there is no update in the account for a given month, a value of zero is considered for the serie that month, while it would be more logical to keep the latest kown value.

[andreasgerstmayr]

Ah! Sorry, I didn't realize the values are a cumulative sum. In that case, your original code was correct.
I think this might be a bug in the echarts library then, maybe you can follow up there? Sounds like the connectNulls setting doesn't work with stacked lines.

[diyanei]

Hi all, just stumbled on this issue, which inspired me to make my own stacked area graph.

I actually found that echarts is not the "culprit" in this case. The issue seems to come from the fact that months without transactions do not generate an entry in the BQL query result, hence data is empty for echarts (and spiky thingys appear).
The resulting javascript data in Firefox debugger (for instance) only has data from months with transactions.

Unfortunately, this directly comes from Beancount way of reporting balances, and, according to various posts from the author, is the expected result, thus this means the missing month entries would need to be filled (with last available balance ?) by the dashboard generator.
I happen to have account which were recently closed, and AFAICT I have no obvious way to make a distinction between no transaction months, and closed accounts... (but your YMMV).

Many thanks @andreasgerstmayr for sharing your dashboards on github, this helped me a lot !

[Evernight]

Hi all. Just came across this, and I've had similar issue (if I understood correctly what's reported here) as well.
However I didn't spend too much time investigating and made a quick hacky fix for accounts with this issue using recur plugin: https://github.com/Evernight/lazy-beancount/blob/main/example_data/regular_postings_fix.bean#L6. Basically, it just creates zero or almost-zero transactions at regular intervals automatically. Maybe it will be helpful for someone else as well.

Obviously it's just a workaround, would be nice to fix this the right way eventually.

Also even more important task for me would be to make a dashboard that reports on a daily or weekly basis and I figured that I would likely face this issue again while doing so.