Visual Display of Vehicle Numbers over Three Years

Hi Everyone,

I am attempting to visually illustrate the fluctuation of vehicle movement to aid a Disabled Driver Parking problem.

The data involves ----

(a) vehicles present (or not) at two different locations. (Upper and Main)
(b) number of vehicles recorded at half-hourly intervals throughout each (24hr) day of the week. (An Upper and a Main value (even zero) required at each interval)
(c) 2D-Column graph to display the comparative variations/concentrations over a three-year period. (Upper and Main per Half-Hour x 24 hours x 7 days x 3 years.)

Quite honestly I haven't a clue how to achieve it and my brain has gone dead after compiling all the data.

I'd be grateful if someone could point me in the right direction for graphing that lot please.

Thank you,

Ian

Sounds complicated.
A good answer would need a representative file from you to be attached.

Hi p45cal,

Thanks for responding to this problem.

You are quite right -- it is complicated.

However, realising its complexity, I am currently working out how the data can become more "finely tuned", hopefully resulting in a reduction in possible Groups and Comparators.

I don't have a file ready just yet and don't know whether I should close down this thread and return with a new enquiry at a later date.

What would you suggest please?

Ian

Continue with this thread, it's not that old, I'm subscribed to it so I'll be alerted when a new message appears in it.

Sample Data File as Requested

Hi p45cal,
Herewith the example data for the Disabled Parking analysis/illustration I am trying to produce.


There are worksheets similar to this one for each day of the week and, in an ideal world, I would like to display a comparison/graph to incorporate all days at once.



To try and reduce the "times" for recording vehicle presence, I have produced TimeZones so, over a period of 24 hours, there are now only 6 Zones involved. (A19-C:25)



Although there is an obvious distinction between numbers in a "Main" and "Upper" location, there is no comparison necessary as they do not compete.



I am hoping it will be possible to display --



{On Monday 12th September, in 2016, in TimeZone 5, in the "Main" location, 7 vehicles were present, whilst on the same date, in the same TimeZone, in the "Upper" location only 3 were present (or no data available)} with a matching display for 2017 & 2018 too --- the whole thing being repeated to cover the complete calendar year for the three year span.



I realise there is an enormous amount of data to be displayed and, if necessary, the graphing could be reduced to displaying one month at a time over the three year span.



Hope it all makes sense?

Best regards,

IanView attachment Sample_Data_Formula_Disabled_Parking_10092018_001.xlsx

Hi p45cal,
I hope this doesn't turn out to be a duplication for I thought I sent this reply yesterday -- but will now assume it is down to my grey cells weakening that gave me the thought.

Herewith the example data for the Disabled Parking analysis/illustration I am trying to produce.


There are worksheets similar to this one for each day of the week and, in an ideal world, I would like to display a comparison/graph to incorporate all days at once.

To try and reduce the "times" for recording vehicle presence, I have produced TimeZones so, over a period of 24 hours, there are now only 6 Zones involved. (A19-C:25)

Although there is an obvious distinction between numbers in a "Main" and "Upper" location, there is no comparison necessary as they do not compete.

I am hoping it will be possible to display --

{On Monday 12th September, in 2016, in TimeZone 5, in the "Main" location, 7 vehicles were present, whilst on the same date, in the same TimeZone, in the "Upper" location only 3 were present (or no data available)} with a matching display for 2017 & 2018 too --- the whole thing being repeated to cover the complete calendar year for the three year span.

I realise there is an enormous amount of data to be displayed and, if necessary, the graphing could be reduced to displaying one month at a time over the three year span.

Hope it all makes sense?

I've only just seen your post - it was in moderation and I don't know why!
I'll try to get time to look at it soonest. Can you confirm you haven't already solved it?

In the meantime, a taster, attached is your file with a copy of your sheet with the data re-arranged with a bit of cut and pasting (this could be automated too) and a pivot table in the vicinity of cell H1.
I think you're using Excel 2007 so there are interesting additions since that version (slicers) that could make it more user-friendly to filter your data within the pivot table, but the possibilities with how the data are shown are huge.

I don't see how you get: {On Monday 12th September, in 2016, in TimeZone 5, in the "Main" location, 7 vehicles were present, whilst on the same date, in the same TimeZone, in the "Upper" location only 3 were present (or no data available)} because your raw data don't support that.
I've left the pivot table filtered for those criteria and I only get 3 vehicles in the 'Main' section.

oh well… looks like IanDGreen lost interest.