Filter Chart Block by Recent Dates

Um, what sort of chart? If you mean, say, a bar chart showing the number of daily orders for a sliding window encompassing a week, then your second approach is best.

Here’s how I recommend doing it. This implementation is based on the procedure(s) detailed in my recent Show and Tell post regarding multi-record calculations. In your case, you aren’t using data from multiple records in the same calculation; instead, you’ll make use of the same mechanism in order to create a sliding window of however many days you wish charted that updates automatically.

This approach requires the creation of two new tables and the creation of a link between every record of an existing table and the single record in one of the new ones; for more on the latter requirement, see the detailed description below. It’s based on a reasonably strict assumption, below. If this assumption does not hold true for your base, you will almost certainly still be able to use the routines I describe, but the formulas I give will need to be modified.

Assumption: Your base contains a table with an record created every day (or every workday). That record contains a date field {Date} containing that day’s date and a number field {Orders} representing the number of orders logged for that day.

1. In that table (which I will call [Main]), define a new single-line text field called {Link to Calc}.

2. Set the value of {Link to Calc} for every record in the table to equal '.' — that is, the period character. This can be done by entering '.' in the first row and dragging the fill handle to the table’s last row; by entering '.' in the table’s first row, selecting that cell, pressing Ctrl-C to copy the value, selecting {Link to Calc} for the second row, scrolling to the last row in the table, and, while holding down the Shift key, pressing Ctrl-V to paste the value in the remaining rows; or by hitting the sequence '.'-Return a number of times.

3. Scroll to the top of the table and right-click on {Link to Calc}; select ‘Customize field type.’

4. Change the field’s configuration from ‘single-line text’ to ‘Link to another record.’ When prompted, select ‘+ Create a new table’ and specify [Calc] as the table name.

5. Airtable whirs for a moment and then converts each of those '.'s into a link from [Main] to a single record in the newly create [Calc] table with the {Name} of '.'.

6. Note: Going forward, every record added to [Main] will need to have a link to [Calc] defined. This can be automated using a middleware offering such as Zapier, if desired — but to create the link manually requires only two additional mouseclicks, otherwise.

7. In [Main], define a new formula field, {DayOrder}, with the following formula.

   This supports up to 999 orders placed on a given day. If you wish to chart the dollar value of orders, you’ll need to modify this code (see this post and associated base for code and instructions). Similarly, if you need only to support up to 9 or 99 orders a day, you may change the number of fixed digits (here, ‘3’); however, you’ll also need to modify the decoding routines, later. (Again, for more information on how this and related routines work, see this post.)

(VALUE(
    DATETIME_FORMAT(
        Date,
        'X'
        )
    )/86400)&
    ':'&
REPT(
    '0',
    3-LEN(
        Orders&''
        )
    )&
Orders

8. In [Calc], define a new rollup field, {DayOrders} that uses the {Main} link to rollup the {DayOrder} field using the aggregation function ARRAYJOIN(values).

9. In [Calc], define a new rollup field, {Latest} that uses the {Main} link to rollup the {Date} field using the aggregation function MAX(values).

10. Create a new table, [Chart]; delete the {Notes} and {Attachments} fields. Create as many records as you wish days in your chart.

11. Define the following fields within [Chart]:

    1. {Index}. Autonumber.

    2. {Link to Calc}. Configure as in Steps 2 through 5, above.

    3. {Latest}. Rollup using {Link to Calc} to rollup the {Latest} field using the aggregation formula (VALUE(DATETIME_FORMAT(values,'X'))/86400)&''

    4. {DayValue}. Rollup using {Link to Calc} to rollup the {DayOrders} field using the aggregation formula

       MID(values&'',FIND(Latest&':',values&'')-((Index-1)*10),5)

    5. {Orders}. Rollup using {Link to Calc} to rollup the {DayOrders} field using the aggregation formula

       VALUE(MID(values&'',(FIND(Latest&':',values&'')+6)-((Index-1)*10),3))

    6. Reconfigure the primary field as formula field {Day} with formula

       DATETIME_PARSE((VALUE(DayValue)*86400)&'','X')

12. Finally, configure your Chart Block using the [Chart] table; chart type Bar or Line; X-axis {Day}, Y-axis Field {Orders}.

Now, as new records are added to [Main] — and linked to [Calc] — the Chart Block will display a sliding window of X values, where X = the number of records defined in [Chart]. The window is based upon the most recent X number of [Main] records, and not a specified number of days; skipped (or weekend) days are ignored. Records in [Main] older than the Xth record can be archived at any time without impacting the chart.

Obviously, I’ve implemented this chart of recent orders according to one set of assumptions; any number of variations (dollar value instead of count; sliding window of calendar days instead of window of recent entries; and so on) could be defined using these sames routines with slight modifications. Again, the post describing these techniques can be found here; a demonstration base implementing the one described in this post (including an example Chart Block) can be found here.