Help needed creating charts based on date

[bitflip]

Hi guys, I have been tasked with creating a dynamic pie chart which can be altered to display data based on day month or year.

The database I have to work with uses a comma deliminated file which is quite large and continuously growing.

Could you guys help a out a newbie who has been up all night trying to figure this thing out

Here is what the data looks like once imported into Excel.



http://imageshack.us/photo/my-images/837/databaser.jpg/

 

[bitflip]

Sweet I had gotten at least as far as naming the range. I used the named range to create the PIVOT table but the result was kind of ugly.

I am not sure if having the date and time both in column B of the source causes issues for the PIVOT table.

 

[geospatial]

Sweet I had gotten at least as far as naming the range. I used the named range to create the PIVOT table but the result was kind of ugly.

I am not sure if having the date and time both in column B of the source causes issues for the PIVOT table.

It wouldnt cause a problem unless the column is not formatted as a date properly. If it is text you will not be able to group in a pivot table.

 

[bitflip]

I think I am still having trouble with this, I want to be able to pick a DATE (day, month or year), pick a ROOM and view a list of the CALLS that have been made. I am especially interested in seeing the call durations.

What type of chart would let me do this, or am I going about using the PIVOT table the wrong way? I can only seem to get a count from the PIVOT table and not individual call durations

 

[bitflip]

Are you guys making a pivot chart for each and every chart you wish to produce?

I am still having trouble getting the durations to display, it always seems to just be a count.

 

[Trevor G]

Ok I have had a think about this, if you want options to look at days, or month or year you are going to need to split the data down with some additional formula. See below.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 37px"><COL style="WIDTH: 128px"><COL style="WIDTH: 102px"><COL style="WIDTH: 75px"><COL style="WIDTH: 155px"><COL style="WIDTH: 75px"><COL style="WIDTH: 30px"><COL style="WIDTH: 48px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Item</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">AT</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">CALLKIND</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">LOCATION</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">MSG</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">DURATION</TD><TD>Day</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Month</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Year</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31/03/2011 11:27</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PATIENT CALL</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WARD-1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">HOSPITAL ROOM H 25</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">00:00:01</TD><TD style="TEXT-ALIGN: right">31</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31/03/2011 11:32</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PATIENT CALL</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WARD-1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">HOSPITAL ROOM H 25</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">00:00:11</TD><TD style="TEXT-ALIGN: right">31</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31/03/2011 11:33</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">ZONE CALL</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WARD-1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">AMBER WING</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">00:00:05</TD><TD style="TEXT-ALIGN: right">31</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31/03/2011 11:34</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PATIENT CALL</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WARD-1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">BED 205</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">00:00:36</TD><TD style="TEXT-ALIGN: right">31</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=DAY(B2)</TD></TR><TR><TD>H2</TD><TD>=MONTH(B2)</TD></TR><TR><TD>I2</TD><TD>=YEAR(B2)</TD></TR><TR><TD>G3</TD><TD>=DAY(B3)</TD></TR><TR><TD>H3</TD><TD>=MONTH(B3)</TD></TR><TR><TD>I3</TD><TD>=YEAR(B3)</TD></TR><TR><TD>G4</TD><TD>=DAY(B4)</TD></TR><TR><TD>H4</TD><TD>=MONTH(B4)</TD></TR><TR><TD>I4</TD><TD>=YEAR(B4)</TD></TR><TR><TD>G5</TD><TD>=DAY(B5)</TD></TR><TR><TD>H5</TD><TD>=MONTH(B5)</TD></TR><TR><TD>I5</TD><TD>=YEAR(B5)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

This will then allow you to use some dynamic filtering to apply that to a chart, so I think the way around this is via VBA.

You would need to be able to change the Chart source to what you select, so the data that you select would have to go onto a separate sheet and each time you make a selection you would need to delete the previous data on the additional sheet and refresh it with the filtered data.

What is your VBA skills like?

You wouldn't be able to use a PIE chart as this will only allow you to look at data either 1 Column or 1 Row.

 

[bitflip]

Hi I don't know allot about VBA other than what snipits of code I can find online.

I found the below code to count the current number of rows and auto fill the DAY, MONTH, YEAR formulas when the workbook is opened. The problem I am having is making this code only apply to the first worksheet.

Private Sub Workbook_Open()

Dim LastRow As Long
LastRow = Cells(Rows.count, "A").End(xlUp).Row

    Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)
    Range("H2").AutoFill Destination:=Range("H2:H" & LastRow)
    Range("I2").AutoFill Destination:=Range("I2:I" & LastRow)
End Sub

 

[Trevor G]

You have to state which sheet to select the very same as clicking on a sheet name with the mouse.

So you would add

Sheets("The Sheet Name").Activate

at the beginning of of your code.

 

[bitflip]

Ok I have had a think about this, if you want options to look at days, or month or year you are going to need to split the data down with some additional formula. See below.

This will then allow you to use some dynamic filtering to apply that to a chart, so I think the way around this is via VBA.

Hi Trevor, thank you for all the help thus far. I have managed to split the day month and year into separate columns like you suggested. Not sure what to do next with regards to dynamic filtering of a chart? Seems like a big next step and my googlefu doesn't seem to be helping.

 

[Trevor G]

I will email you and once we have a solution we will put the results back on the thread.

You will need to use Filters, Additional Sheets, Charts and some code.

 

[bitflip]

Really... wow that is incredibly helpful of you. I can give you a bigger slice of the data to work with if you think it might help?