Create a chart to cover the last 2 weeks

The end date of the chart would be now() not 19Dec3019
Is this possible?


you can’t achieve this directly by options of the chart module (may be I should add “to my best knowledge”).
A solution for this requires a dynamic excerpt of the last 14 days into a special fixed range containing the data (last 14 days) and which can be referenced by a chart.

The following sample file has two different solutions using this idea ChartMovingData.ods

Assumptions: (for the sample file)

  • The original data are in sheet Data
  • The dates in sheet Data are ordered by date in ascending order
  • Data for TODAY() are available in sheet Data
  • Sheet Chart contains the prepared data (from sheet Data to be used in the dynamic chart)
  • How to enter array functions is known (required for Solution 1)
  • The only input could be Data End (yellow background)
  • The sample file is elaborated for 1 data column only, and thus would require adaption if you got more than one value (column) for a single date.

Solution 1 - Dynamic Named Range

The solution

  • Defines a dynamic named range DAYS14 (see Sheet -> Named Ranges and Expressions -> Manage, don’t want to repeat the formula here, just see in the file)
  • Uses this dynamic named range in cell as array function {=DAYS14}
  • Defines a chart using range $Chart.$B$6:$C$20
  • Cannot be easily adapted to more than 14 days (of course possible)

Solution 2 - Dynamic Data using OFFSET

This solution

  • Evaluates, based on input, the cell in sheet Data containing the date 14 days ago
  • This cell is the base for referencing 14 offsets down and 1 right to create the dynamic data table (see cell right to label OFFSET Base
  • Defines a chart using range $Chart.$B$31:$C$44

Hope that helps.

If the answer helped you to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

Pivot chart could possibly also be useful (not automatic, but easy to show what you need :-))