Graphic with Days and Hourly Data

[Michele_Ancis]

Hi all,

I have some data in the following form:

The date format is understood by the software, the "hours" column
is just numbers. I would like to condense these two into a unique
data column with days and hours. I am trying to produce an hourly
graph, day after day.
If I thy this now, using the first column as x-data source lines up all
24h data on one x point, using the "Hours" data I can't distinguish
between days.
I have tried to join strings into a new cell - which would then have
a "dd/mm/yyyy hr:mm" format, but the date format gets interpreted
and gives some number when treated as a string.

Any suggestions on how to do this?

Thank you in advance,

Michele

[acknak]

Make a new column that combines the date and time.

If the date and hours are in columns A & B, =A1+B1/24 will give a date+time value. You can apply a suitable format to the result to see it nicely.

[Michele_Ancis]

Great!

Out of curiosity: why do I have to
divide the hour by 24?

Thank you!

Michele

[acknak]

Dates are stored by Calc as an integer number of days; times are simply a decimal fraction of one day. A date+time value will be a non-integral number of days--a number of days plus a fraction of a day representing the time.

So to add a time to a date, you just have to convert the time to be added to have the same unit: days. Hours/24 converts the number of hours to a fraction of one day. You could also use the TIME() function: =A1+TIME(B1;0;0), but it's simpler to just divide by 24.

For more: http://wiki.services.openoffice.org/wik ... e_overview

[Michele_Ancis]

Great Stuff!

Thank you again!

M

[Michele_Ancis]

...oops, I think I blew my horn too early
while the data look now correct, the
graph doesn't

somehow the data aren't displayed with
an uniform x-axis but grouped in clusters.

Do you have a great answer for this as well

Thanks!!

M

[acknak]

Maybe it will help to see an example...

[Michele_Ancis]

Yes indeed it helped

However, I think I spotted a bug. The reason why it wasn't working with me is the following.

Between my x and y-data column I have some columns containing text. My usual way to set up graphs
is to select the columns of interest, then click on the graph button.

This is what I did this time as well.

Now, this is the thing (I can reproduce it)

- If I select two non adjacent columns (by pressing ctrl) and use the graph, what comes out is a graph of the type I showed (with data "clustered" some way).

- If I - however - organize columns so that the data to put in graphical form are all adjacent, I get the kind of diagram you posted me as example, which is also what I was expecting.

Interesting, isn't it?

M

[acknak]

Sorry, I'm not seeing a problem if I insert a column of text stuff and follow the steps you describe.

It might be easiest if you could just attach your sheet. Cut off a bunch of the data if you want/need to; just leave enough to test.

You can use the "Upload Attachment" link (below the message entry area after you click "POST REPLY"). [Forum] How to attach a document here

[Michele_Ancis]

Well,

I've got a new theory
I've tried what I described with
your file and everything
went O.K.

The new theory is that I was
working with a csv file...Don't
know actually. Anyways, here's
the file.

Vtest.odt

(4.13 KiB) Downloaded 121 times

the csv is not allowed so I had
to change it to odt. Please change
it back before opening.

[acknak]

Ok, I see now. You get those spikes because that's what your data look like

Your data set includes two 24-hour periods that are almost a month apart. Any graph that includes both periods against the same time axis will compress the data into two narrow regions at either end.

I don't know what the chart is intended to show, but one possibility is to overlay the two data sets--plot time vs. impressions, with two lines, one for each date; something like this:

date+time_sample_data.png (9.14 KiB) Viewed 4348 times

[Michele_Ancis]

Awright...

Now I recall I had the same problem some
time ago (well...kind of).

This was my fault: I chose the wrong format
while importing the data into calc. I chose
day/month/year instead of month/day/year
(we use the former format in my homeland )

Since the source data are indeed continuous, I
didn't pay attention to this discrepancy

Thank you again for your patience!!

Michele

[acknak]

You mean the data in the file are written in two different formats? Because I see:
01/31/10
01/02/10

So, the first set has to be m/d/y, and the second set would have to be d/m/y to make it 31-Jan & 1-Feb.

I never considered that the formats might be messed up

[Michele_Ancis]

Yes indeed.

I don't know exactly at which
point, but at some point along
the chain, this "crossover" happens.

The source of csv data is Google.
I then open the file with Calc and
it can happen that the first date
data are correct, while the next
get crossed.

Although this was the first time
I wanted to look at hourly data,
at another time in the past I had
the same problem with months/days
getting crossed.

This specific time, I know I could
have chosen a date format instead
of "standard" while importing, because
I hoped this way to get Calc
understand that I wanted the
dates organized in hourly intervals.

This is however not very clear, as
I normally apply format choosing
the column and not the data in it.

As you say, therefore, *all* data
in the column should have been
interpreted the same way.

I have done this process only two
times across months, so I am not
aware of all details, I could have
messed up things along the way
without recognizing it.

I will however analyze my data
more often now, so I guess I am
going to find out where is that I
am causing the problem.