Showing dates on chart horizontal axis that are missing from table

1

Hi there,

I have a table where I record data on a daily basis. It happens that some days will be missing entries.
I also have a chart where the horizontal axis is days and the vertical axis is a sum of daily totals.

The chart renders the data nicely for days that have entries, however I would like to display days that don’t have any data.

Any idea how I could display consecutive days in the chart without needing to alter the actual data table?

2

Dear @Levi_Kovacs, welcome to the Coda community :handshake:

A few questions:

  • Will you be able to share a dummy copy of your document?
  • Assuming that you already have somewhere a chart, how did you handle the empty dates there?
3

Thanks for the welcome @Jean_Pierre_Traets!

So the data looks something like this:

Type, Date, Value

Sale, 20/12/2019, $200
Renewal, 20/12/2019, $500
Sale, 22/12/2019, $400

If I chart this where the horizontal axis is dates and vertical is sum of value, it leaves 21/12/2019 out, which makes sense.

I was wondering if there would be a way of having the days printed continuously including today.

One way I would tackle it is to create a new view with two columns: date and sum. There I would print each day and add up the values into the sum column. Then chart the second view instead of the original table.

Does that make sense?

4

Dear @Levi_Kovacs

I created a sample below where I just added a sequence of all days, including days without having data on it. And as you can see they are properly displayed :eyes:

5

Hi @Jean_Pierre_Traets

Thanks for this! This was how I was going to tackle it, to create a second table that includes empty days.

My initial table that I write into with Zappier looks like this:

See that days where no data is available, no row will be added.

6

I think @Jean_Pierre_Traets has the best solution for you. There isn’t a way to tell the chart to include days that are not in the data.

A workaround might be to add an automation that would run every morning though. It could filter to see if there is an entry for the previous day and if not, it could add a row for that day with a 0 value.

7

It’s not ideal, but if you are willing to change the type of chart to Line Chart or Area Chart, this is done automatically

Show All dates
Show All dates857×519 270 KB

8

Nice! Thanks for the alternative. That’s good to know.

Appreciate the help @BenLee and @Jean_Pierre_Traets :+1:

2 Likes