[Solved] Charting multiple values over a date range

[dc2010]

I've got a couple of years worth of data from two different sources ( A & B ). These are different values but I would like to be able to plot them on the same chart. Each value has a data associated with it but not every date has a value. Some dates have an A value and some dates have a B value some dates have both and some neither. Is there a way to create a date based line chart that plots the A & B values over the entire date range? I'm a novice when it comes to OpenOffice but I do know how to create two distinct charts one of which shows A and one of which shows B.

Thanks.

[FJCC]

I'm not sure whether you want to chart the A and B values as two series or one. If it is two series, then you need to make a single Date column and put the corresponding A and B values in two columns. So you might have your dates in column D, A values in column E and B values in column F. Some cells in columns E and F will be empty. You will then probably want to sort all three columns so that the dates are in ascending order and make an XY scatter plot. Does that get you what you want?

[dc2010]

Thank you.

That's not bad. Maybe I should consider the question answered at this point. However, as you point out, after doing the sort, lots of cells in columns E and F are empty - any row that has a value in column E is empty in column F. This makes it difficult to do any other work with those values since they are now intermixed with empty cells.

I was hoping to find a way to chart these numbers on the same chart without modifying the layout of the original data.

[jrkrideau]

Thank you.

That's not bad. Maybe I should consider the question answered at this point. However, as you point out, after doing the sort, lots of cells in columns E and F are empty - any row that has a value in column E is empty in column F. This makes it difficult to do any other work with those values since they are now intermixed with empty cells.

I don't understand this point. It should not make any difference if you have missing data if you are using what Calc calls an XY(Scatter) Chart.

I was hoping to find a way to chart these numbers on the same chart without modifying the layout of the original data.

What same chart?

See attached example

[dc2010]

I don't understand this point. It should not make any difference if you have missing data if you are using what Calc calls an XY(Scatter) Chart.

The chart works fine but if I want to do *other* work it gets trickier. Suppose, using your attached example, I want to calculate the change between successive values in the A column. If I have no missing data, then I can create a formula to subtract an A value from the value below it and copy that formula repeatedly to get a column of changes ( see my attached example ). When I try to do this with the A values that contain the "missing data", the missing data gets incorrectly interpreted as zero values and the deltas are wrong. That's just an example off the top of my head.

What same chart?

From my original question: "These are different values but I would like to be able to plot them on the same chart." So, as you did in your attached example, I would like to be able to plot the A values on a chart and plot the B values on the same chart. That's what I meant by the "same chart".

See attached example

Thank you. Your attached example has no dates that have a value for both A and B. In my real world application, most dates had values for both A and B. This resulted in lots of dates that were repeated after I did the sort suggested by FJCC. I know this is nit picky but I think the repeated dates look bad and hint that the combining and sorting technique is flawed.

It seems like this is important functionality. There must be lots of situations where someone gathers data from various sources over time and wants to be able to visually compare it even if the time stamps for the data values are not exactly the same. There might be dozens ( hundreds ? ) of such data sources and potentially she might want to graph any one of them against any other. In that case, the merging and sorting technique is not practical.

I'm attaching a modified version of your example that shows the problem with missing data using my hypothetical delta example above. My modified example also contains some repeated dates for A and B values.

[ken johnson]

Your Delta calculation is still doable.
Unfortunately the introduction of empty cells results in a formula that is, as far as I can tell, much more complicated than =B3-B2...

Code: Select all

=IF(OR(COUNTIF(I$3:I3;"<>""")=1;I3="");"";I3-INDEX(I$2:I2;SUMPRODUCT(MAX((I$2:I2<>"")*(ROW(I$2:I2)-MIN(ROW(I$2:I2))+1)))))

in K3 filled down to K27.
The

Code: Select all

OR(COUNTIF(I$3:I3;"<>""")=1;I3="")

part checks that the current A value is neither the first A value nor a blank.
The

Code: Select all

SUMPRODUCT(MAX((I$2:I2<>"")*(ROW(I$2:I2)-MIN(ROW(I$2:I2))+1)))

part determines the position, within all of the previous A cells, the last A cell holding an A value. The actual value of the previous non-blank A cell is determined by the INDEX function with the SUMPRODUCT, MAX, ROW, MIN combo as its second or Row parameter.
Finally, the value determined by the INDEX function is subtracted from the current A value to give Delta A.
Note that in this formula the presence or absence of $ signs prefacing row numbers is extremely important.

april30-2x.ods

(16.88 KiB) Downloaded 97 times

Ken Johnson

[dc2010]

Thanks Ken,

That's both interesting and impressive.

It also backs up my earlier claim that the first proposed solution makes it "difficult to do any other work with those values since they are now intermixed with empty cells." ( difficult - not impossible )

[ken johnson]

Just for fun, another technique is to use a helper column consisting of the A values and the blank cells filled in with the previous A value.
Then a final formula that returns a blank when the current A value is a blank or the difference between the current helper value and the previous helper value when the current A value is neither blank nor the first A value.
In the attached doc the helper column is column L and it is important to note that the top-most cell's formula, =IF(I3="";"";I3), is not the same as the next cell's formula, =IF(I4="";L3;I4) [note that this formula in L4 refers to the value in L3]. This second formula is filled down as far as needed (or further).
The formula for Delta A is in column M and the top-most cell is left blank (as you did with your original Delta A formula before the blank cells were introduced). This formula in M4, =IF(L3="";"";IF(I4="";"";L4-L3)), is filled down as far as needed (or further).

This technique has the advantage of using simple formulae.
With 1024 available columns, the use of helper columns is no problem.

april30-2xx.ods

(18.39 KiB) Downloaded 88 times

Ken Johnson

[dc2010]

After poking around the scatter chart wizard, I realized that the functionality to do exactly what I wanted is available therein. The way to make it work is to pick different X ranges for each Y range. The resulting chart comes out as I described in my original question.

The attached example shows what I'm talking about.

Thanks to everyone who responded.