Calc: how to chart series "slices"? [closed]
This is my challenge: I have several very long time-series in a sheet. Column A is date and columns B-... are samples from sensors. Of course, I can display the raw time-series under various formats and styles.
However, I'd like to see "de-seasonalised" trends in these data. By "de-seasonalised" I mean, e.g., all samples taken at 8:00, 9:00, 10:00 ... as 24 independent series for hourly samples, or all samples for January, ... as 12 independent series for monthly samples.
AFAIK, there is no way to select a range of cells with a step to say "take every other n cells". All I can do is chart B1:B999 and any fancy addition like A1:B999:12 is immediately flagged as an error.
I looked at pivot tables but they can't spread a 1080-element vector into a 12x90-matrix, which would do the trick.
The nearest I got is to manually enumerate which cells are in the series as B1;B13;B25 ... However this is impractical when the series contains more than, say, 10 elements. As I mentionned, I have many series and they are very long.
I'd prefer not to dump the series into a text file and use awk, perl or other macro processor to group the samples differently. I'd like to do it all from inside LO.
Is there a way to define a subset of regularly spaced cells within a range like B1:B10, taking 1 every other n cells, so that this reference can be directly input into the "data range" for a chart?
EDIT: Following @karolus question, my problem is akin to subsampling.
EDIT 2017-03-07: Lupp's updated.ods for @Lupp examination
Sorry. I cannot read this chart. The info added by Max, Average, Min may be of value, and three timeseries sufficiently distinct, I can read. The 24 "old" series ar more like a noise to my eye. They roughly display the "degree of chaos" if anything. Probably much ado about little information.
This is, of course, only an opinion formed by my specific experiences.
In a case of neatly stacked series things may be readable.
I will add a reworked demo derived from yours to my answer.
The intent was not to be able to read the 24 series. As you pointed out earlier, beyond 3-5 series any chart is too "dense" to be of value, all the more as I dimmed the lines. The "degree of chaos" might be the right description for what I did. The only information derived (and it can be derived independently from "chaos" display) is: samples above the average are more widely scattered and less numerous than those below because the interval avg-max is wider than min-avg.