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
Dataare ordered by date in ascending order - Data for
TODAY()are available in sheetData - Sheet
Chartcontains the prepared data (from sheetDatato 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
Datacontaining 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 :-))