The end date of the chart would be now() not 19Dec3019
Is this possible?
Hello
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 sheetData
- Sheet
Chart
contains the prepared data (from sheetData
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
(seeSheet -> 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 () next to the answer.
Pivot chart could possibly also be useful (not automatic, but easy to show what you need :-))