PivotTables

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
All,
I decided it's about time I looked at using these PivotTable thingies.
I've ran into problem already.

I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

The Fields/Headings I'm importing are:

{"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


An explaination of the fields I need to look at currently are:

calldate: is the Date AND Time stamp, combined, of the record
operator: User NT login
DMCYN: Always Y or N

I've been through the PivotTable wizard.

I selected the range tblCallLog, which is a Dynamic Named Range.
I placed:
calldate in Row
operator in Column
and Count of DMCYN

Finish.

Now for my question.
the calldate is in the following format

24/04/2002 18:17:02

therefore every seccond in accounted for.

how do I Group them so the Row part reads

12:00
13:00
14:00

and has the count of TIME>=12:00 and <13:00

Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

this is not very well explained but I can answer any questions as required.

All your help will be greatly received.

Many thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
On 2002-04-30 06:08, Ian Mac wrote:
All,
I decided it's about time I looked at using these PivotTable thingies.
I've ran into problem already.

I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

The Fields/Headings I'm importing are:

{"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


An explaination of the fields I need to look at currently are:

calldate: is the Date AND Time stamp, combined, of the record
operator: User NT login
DMCYN: Always Y or N

I've been through the PivotTable wizard.

I selected the range tblCallLog, which is a Dynamic Named Range.
I placed:
calldate in Row
operator in Column
and Count of DMCYN

Finish.

Now for my question.
the calldate is in the following format

24/04/2002 18:17:02

therefore every seccond in accounted for.

how do I Group them so the Row part reads

12:00
13:00
14:00

and has the count of TIME>=12:00 and <13:00

Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

this is not very well explained but I can answer any questions as required.

All your help will be greatly received.

Many thanks

Hi Ian,

you can add "calculated fields" in your pivot tables which may help..... I've not used them much myself, so you'd proabbly be on the same learnign curve if you attempted to add some to determine the times

(I've seen your posts...you know your stuff !)

Alternatively, could you pop in an extra column in your data to strip the time range from the calldate and include this is the table ?

Similarly, another column in your data with the date stripped out from the call date would allow you an extra field with which to pop into the page field....

apologies if I'm stating the obvious !

(I usually stay well clear of any questions about dates and times, as they are not my fortes by *any* stretch of the imagination!)
 
Upvote 0
On 2002-04-30 06:08, Ian Mac wrote:
All,
I decided it's about time I looked at using these PivotTable thingies.
I've ran into problem already.

I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

The Fields/Headings I'm importing are:

{"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


An explaination of the fields I need to look at currently are:

calldate: is the Date AND Time stamp, combined, of the record
operator: User NT login
DMCYN: Always Y or N

I've been through the PivotTable wizard.

I selected the range tblCallLog, which is a Dynamic Named Range.
I placed:
calldate in Row
operator in Column
and Count of DMCYN

Finish.

Now for my question.
the calldate is in the following format

24/04/2002 18:17:02

therefore every seccond in accounted for.

how do I Group them so the Row part reads

12:00
13:00
14:00

and has the count of TIME>=12:00 and <13:00

Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

this is not very well explained but I can answer any questions as required.

All your help will be greatly received.

Many thanks

Hi Ian,

you can add "calculated fields" in your pivot tables which may help..... I've not used them much myself, so you'd proabbly be on the same learnign curve if you attempted to add some to determine the times

(I've seen your posts...you know your stuff !)

Alternatively, could you pop in an extra column in your data to strip the time range from the calldate and include this is the table ?

Similarly, another column in your data with the date stripped out from the call date would allow you an extra field with which to pop into the page field....

apologies if I'm stating the obvious !

(I usually stay well clear of any questions about dates and times, as they are not my fortes by *any* stretch of the imagination!)
 
Upvote 0
Chis my best mate


tell me whats wrong with times and date, anyone would have thought you dont like them!!!

I would never ask you such a question ............... would i?

:) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :)

_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======
This message was edited by Jack in the UK on 2002-04-30 14:23
 
Upvote 0
On 2002-04-30 14:23, Jack in the UK wrote:
Chis my best mate


tell me whats wrong with times and date, anyone would have thought you dont like them!!!

I would never ask you such a question ............... would i?

:) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :)

_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======
This message was edited by Jack in the UK on 2002-04-30 14:23

Sorry Jack but what has this got to do with my question/post? I think nothing.

If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.


but if you just want to talk to your mate...........

But seriously, if any one can think of a way to do this without striping the date/time, (Chris I hadn't thought of it til a lot later. :biggrin:). The problem I have now is that I wish to do the same with another set of data within the same table working on 20000 rows, which would add a further 40000 enteries to an already fairly heavy file (i.e MSQuery to Excel to pivot).
on the other hand what I have currently is running a lot quick than the SUMPRODUCT() solution I've been experimenting with.

Many thanks,

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-30 15:27
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top