Help - Pivot

Hello,
My Query look like:-

Using the data on the "Raw Data" tab, please use the Excel Pivot Table 
functions to display the total calls Offered by CallTypeDirn by Day like the 
following:

           CallTypeA    CallTypeB    CallTypeC
June 5
June 6            (Calls Offered)
June 7
June 8
June 9
June 10
June 11

Dates should be grouped by Interval and only include the Hours of Operation 
of the project.  Hours of Ops for this data is 6am to 3pm Sat and Sun and 
5am to 9pm Mon - Fri.

And Raw data look like:
      IntervalBegin CallTypeDirn HandleInSL HandleOutSL Offered
      6/5/05 5:00 87883 1 0 2
      6/5/05 6:00 87345 30 0 30

6/6/05  6:00           87883            2                      0 
3


Thank you 


0
7/1/2005 7:00:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
546 Views

Similar Articles

[PageSpeed] 49

What have you done so far, and where are you getting stuck?

Dushyant Rajput wrote:
> Hello,
> My Query look like:-
> 
> Using the data on the "Raw Data" tab, please use the Excel Pivot Table 
> functions to display the total calls Offered by CallTypeDirn by Day like the 
> following:
> 
>            CallTypeA    CallTypeB    CallTypeC
> June 5
> June 6            (Calls Offered)
> June 7
> June 8
> June 9
> June 10
> June 11
> 
> Dates should be grouped by Interval and only include the Hours of Operation 
> of the project.  Hours of Ops for this data is 6am to 3pm Sat and Sun and 
> 5am to 9pm Mon - Fri.
> 
> And Raw data look like:
>       IntervalBegin CallTypeDirn HandleInSL HandleOutSL Offered
>       6/5/05 5:00 87883 1 0 2
>       6/5/05 6:00 87345 30 0 30
> 
> 6/6/05  6:00           87883            2                      0 
> 3
> 
> 
> Thank you 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
7/1/2005 8:21:22 PM
Hi Debra,

Following is the somesnap shot of what  I have done so far, I am not able to 
decide how to make validation in Pivot like if you have given time interval 
of 24 hrs and you have asked to choose interval between 3 am to 9 pm and so 
on.

 Main Prob:- time interval is giving for 24 hrs but I have to select some of 
it and its for 7 days . so how do I make pivot to take selected time 
interval.


      Sum of Offered   CallTypeDirn
      Date IntervalBegin 84864 84884 87133 87345
      6/5/2005       1 795
      6/6/2005   152 66 4 1716
      6/7/2005   116 36   1604
      6/8/2005   104 32 4 1460
      6/9/2005   112 47 2 1520
      6/10/2005   133 54 2 1416
      Grand Total   617 235 13 8511


"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message 
news:42C5A5C2.6000708@contexturesXSPAM.com...
> What have you done so far, and where are you getting stuck?
>
> Dushyant Rajput wrote:
>> Hello,
>> My Query look like:-
>>
>> Using the data on the "Raw Data" tab, please use the Excel Pivot Table 
>> functions to display the total calls Offered by CallTypeDirn by Day like 
>> the following:
>>
>>            CallTypeA    CallTypeB    CallTypeC
>> June 5
>> June 6            (Calls Offered)
>> June 7
>> June 8
>> June 9
>> June 10
>> June 11
>>
>> Dates should be grouped by Interval and only include the Hours of 
>> Operation of the project.  Hours of Ops for this data is 6am to 3pm Sat 
>> and Sun and 5am to 9pm Mon - Fri.
>>
>> And Raw data look like:
>>       IntervalBegin CallTypeDirn HandleInSL HandleOutSL Offered
>>       6/5/05 5:00 87883 1 0 2
>>       6/5/05 6:00 87345 30 0 30
>>
>> 6/6/05  6:00           87883            2                      0 3
>>
>>
>> Thank you
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 


0
7/1/2005 8:36:13 PM
Since the hours of operation are different on weekends, you could add a 
column in the source table, to calculate if the call was during 
operating hours. Then, add that field to the page area of the pivot 
table, and use it to filter the pivot table for only the valid calls.

Dushyant Rajput wrote:
> Hi Debra,
> 
> Following is the somesnap shot of what  I have done so far, I am not able to 
> decide how to make validation in Pivot like if you have given time interval 
> of 24 hrs and you have asked to choose interval between 3 am to 9 pm and so 
> on.
> 
>  Main Prob:- time interval is giving for 24 hrs but I have to select some of 
> it and its for 7 days . so how do I make pivot to take selected time 
> interval.
> 
> 
>       Sum of Offered   CallTypeDirn
>       Date IntervalBegin 84864 84884 87133 87345
>       6/5/2005       1 795
>       6/6/2005   152 66 4 1716
>       6/7/2005   116 36   1604
>       6/8/2005   104 32 4 1460
>       6/9/2005   112 47 2 1520
>       6/10/2005   133 54 2 1416
>       Grand Total   617 235 13 8511
> 
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message 
> news:42C5A5C2.6000708@contexturesXSPAM.com...
> 
>>What have you done so far, and where are you getting stuck?
>>
>>Dushyant Rajput wrote:
>>
>>>Hello,
>>>My Query look like:-
>>>
>>>Using the data on the "Raw Data" tab, please use the Excel Pivot Table 
>>>functions to display the total calls Offered by CallTypeDirn by Day like 
>>>the following:
>>>
>>>           CallTypeA    CallTypeB    CallTypeC
>>>June 5
>>>June 6            (Calls Offered)
>>>June 7
>>>June 8
>>>June 9
>>>June 10
>>>June 11
>>>
>>>Dates should be grouped by Interval and only include the Hours of 
>>>Operation of the project.  Hours of Ops for this data is 6am to 3pm Sat 
>>>and Sun and 5am to 9pm Mon - Fri.
>>>
>>>And Raw data look like:
>>>      IntervalBegin CallTypeDirn HandleInSL HandleOutSL Offered
>>>      6/5/05 5:00 87883 1 0 2
>>>      6/5/05 6:00 87345 30 0 30
>>>
>>>6/6/05  6:00           87883            2                      0 3
>>>
>>>
>>>Thank you
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
7/2/2005 1:37:38 AM
Reply:

Similar Artilces:

I'm a New user Asking for help!
how do i mutiply more than one column each time. Hi not really sure what you're trying to achieve. Could you give an example -- Regards Frank Kabel Frankfurt, Germany "09lawrencej@gaggle.net" <09lawrencej@gaggle.net@discussions.microsoft.com> schrieb im Newsbeitrag news:8503AC40-616A-493B-BAA3-6B9D4BB27512@microsoft.com... > how do i mutiply more than one column each time. What do you really mean? Do you want to multiply several columns wit the same number? 09lawrencej@gaggle.net Wrote: > how do i mutiply more than one column each time -- BenjieLo -----------...

I don't see the Pivot table field list window
Hi, I am trying to generate a chart from an excell file which contains my data. After selecting all rows and columns, I go to Data > Pivot table and pivot chart report. Wizard show that I ahve selected the Excell list and Pivot table. But when I click Finish button, it does not show the Pivot table field list window. How can I view this window to drag data for the rows and columns in order to get the report/chart. This works on my wife's computer but does not work on my computer at my work. Thanks, Zafar Hi Zafar, Most likely it has been turned off. Display the Pivot Table to...

help with datedif function please
I have names and dates in columns , every time the name changes I need the difference from that date untill to day's date. With the same name I need the difference between the dates eg: A b c 1 vit 05/12/2009 datedif untill TODAY() 2 vit 20/11/2009 datedif between A1-A2 3 vit 04/07/2009 " " A2-A3 4 vit 24/06/2009 " " A3-A4 5 vit 15/06/2009 " " A4-A5 6 tom 21/11/2009 d...

Lost all my Outlook data after a certain date....HELP!
Outlook 2000 has screwed me over big-time! I seem to have lost all my Outlook data for the past 6 months - email, contacts, calendar, etc. All the data prior to 6 mos. ago has been retained. Right before this data loss, I was having strange PC behavior - most likely due to spyware apps that were slowing down performance. I looked at my Outlook PST file and it appears the same size as it was before the data loss, so this missing data should still be in there somewhere? I have tried to export the PST file to different formats, such as Access and tab-separated values, but to no avail. I thi...

A little help with a LNK2019 error?
Recently turned UNICODE on in my project. Builds fine in debug mode. Release gives me only this error on build. " .... error LNK2019: unresolved external symbol __imp___CrtDbgReport referenced in function "public: unsigned short & __thiscall ATL::CSimpleArray<unsigned short,class ATL::CSimpleArrayEqualHelper<unsigned short> >::operator[](int)" (??A?$CSimpleArray@GV?$CSimpleArrayEqualHelper@G@ATL@@@ATL@@QAEAAGH@Z) I can't quite comprehend it. The help system has this.. LNK2019.. In Visual C++ .NET 2003, this error will be generated when /clr is used...

help with 2 xl sheets...
I need help with 2 worksheets. first sheet is a time schedule sheet. I need to count the total number of shifts entered in a week The sheet is set up so columns are days of weeks, each person has 2 rows, top row is the start time bottom row is end time, time is entered is in 24Hr format and shifts start at various times of days. Second I have a row of numbers 1 to 20 that is averaged in row 21 in column A. Row 1 has the most current figure, row 20 is oldest, Can I set it up so I can enter the most current number in row 1 column A and all the existing figures automatically bump down 1 row? ...

Need help setting up a leave copy on server
I have an exchange server. I'm trying to setup an option where I can leave a copy on server, so that the client can recieve the same E-mail on the website and on his computer at work. How can I configure that option on outlook meanwhile having an exchange server? Don't use a .pst file and the mail will remain in the exchange mailbox. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, ...

PIVOT TABLE CHARTS
How can I stop a chart reformatting when I refresh a pivot table This is a known problem with PivotCharts. There's information in the following MSKB article, which suggests recording a macro as you apply the formatting: XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/?id=215904 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "EOCLCV" wrote: > How can I stop a chart reformatting when I refresh a pivot table How can I solve this problem if I am using the PivotChart webpart in Sharepo...

help importing cell
Hi can someone help me with the formula for importing the value of cell from one worksheet to another. For example the value of cell a1 from worksheet x to current sheet I believe the formula starts with ~ thanks in advance: -- eliosta ----------------------------------------------------------------------- eliostar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3251 View this thread: http://www.excelforum.com/showthread.php?threadid=52304 Hi You can use something like this: =sheet1!A1 in the cell to show what is in that cell on your current sheet. Andy. ...

Pivot Table Page Field
Hi There, I have a simple pivot table that shows sales by salesperson, per month. Months are represented by accounting period numbers (i.e. apr =1, may=2, .....march =12) The period numbers are the page field, so i select a period from the page field and it shows me the sales for that month by salesperson. However, I have the period in cell A1 that changes in real-time, can I link the page field to this cell, so instead of me having to increment the period. It simply picks it up from A1. Any help is appreciated Jim ...

Help me impress my boss PLEASE
Heres the thing: We have 3 people and our boss. The boss needs to be able to send a task out that re-accures every month, we the people need to have one of us accept the task, and modify the status to show that it is in progress. Everyone else must see that this has been done by using a popup. Can I have a pop up window show that someone has accepted the task? And if no one accepts it and the task sits for 1 day, can I have a popup window appear saying "MUST BE DONE!!!" I'm a little confused can someone recomend a really good web page? To sum up: 1. Pop Windows are th...

need little help with MSXML
Hi I am using MSXML in my VC++ project.My problem is that I will be having some XML documents and in those documents I need to get the data inside the <title> tag and <link> tag.this two tags will be always inside <item> tag.But they will not be in any order.Previously the code I was using is: if(NODE_ELEMENT==pChild->nodeType) { if(_bstr_t("item")==pChild->nodeName) { pChild=pChild->firstChild; //assumed that title tag is first tag m_node.push_back(pChild->text); pChild=pChild->nextSibling;...

New to accounting software, help please.
I currently don't use any accounting software but need to start using it. I live in the UK but can't seem to find any software versions post 2005, does anybody know if there are any plans to introduce money 2007 in the UK? If not, would I be able to download the US version and work adequately in the UK? Many thanks Sean. M2005 is the latest UK version. There are no plans to introduce the US versions. US versions are heavily customised for the US and apart from the obvious such as mis-spelling of cheque ;-) unless you plan to keep usage very simple you may find the US versio...

VLOOKUP HELP 03-02-10
I need VLOOKUP to search across worksheets where the names are not the same (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A) DoeJK. Can I have VLOOKUP find each name across worksheet 2 and 3? Here's what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes"). Thanks for any help. =vlookup() expects the table to be on a single sheet--not three different tables on 3 different sheets. But if you're just trying to determine what sheet that name is located on, you could use three formulas like: =isnumber(match(a2,...

using variable length named ranges problem
I have 3 variable length lists of data that need to be concatenated in to one column. Presently the following formula works (This is typed in to A1 and copied down the column using the drag handle so in cell A2 refs to C1,D1,E1 are automatically changed to C2,D2,E2 and so on): =IF(ROW()<=COUNTA(C:C),C1,IF(ROW()<=(COUNTA(C:C)+COUNTA(D:D)),OFFSET(D1,(COU NTA(C:C)),0,1,1),IF(ROW()<=(COUNTA(C:C)+COUNTA(D:D)+COUNTA(E:E)),OFFSET(E1,- (COUNTA(C:C)+COUNTA(D:D)),0,1,1),""))) Result - a list of data in column A which is the list of data in column C plus data in column D plus ...

Grouping query help
I know I can do this using a sub query but I'm trying not to Basically I got three feilds of data. One of the feilds is a dat feild and I'm trying to group the data on the most recent date. For example: In the data below, I would like the query to display for each category, the actual rate on the most recent date (for that particular category). Category Date Rate CIT 4/1/2004 4 CIT 7/1/2004 4.25 CIT 8/9/2005 6.5 L30 11/10/2006 5.32 LAS 5/24/2004 4 LAS 7/1/2004 4.25 LAS 2/2/2005 5.5 LAS 3/22/2005 5.75 STD 5/24/2004 4 STD 7/1/2004 4.25 STD 9/21/2004 4.75...

2007 Excel Table and Pivot Table Formatting
Hi, Just wondering why when I use the standard formatting (ie design ) designs in excel 2007 for tables and pivot tables, when I send this to someone that does not have excel 2007, they get this with no colour, no bold subtotals etc. Thanks ...

Help! Money 2005 doesn't work after update!
installed 2005 premium two weeks ago and set up all my bank info. I got notice for update every time but I canceled it. But later, since my bank info was set, I updated it. Then the trouble came. I can not log in anymore. EVery time after I input the passport, it gave error as "an unexpected error has occurred. please try your call again. If you continue to have problems call your financial institution's online service customer support." I tried to uninstall and reinstall. The same problem. so I can not use it any more. Help! See http://money.mvps.org/faq/article/42.aspx and ...

Newbie Help: customising forms
I'm new to CRM and setting up a test deployment in our office. I need some help please and can't find anything in the manuals to assist me. (It's probably there but I can't find it!). When an account is created, the OWNER information is automatically updated with the name of the user creating the account. We've added a custom field: LIVE DATE, and I wanted to add a further field APPROVED BY: The APPROVED BY field would have a user name of the person who passed this account to go live. Ideally, I'd like another form (we currently use an Excel based Checklist which I...

Help! Ungrouping Excel Charts --> EMF/WMF. Can't edit objects in 2007 (Freeform issue). Steve Rindsberg?
Hi, I am posting this again in a hope to get attention to my tricky problem Bob _________ Thanks Jon, Some consolotion, but no solution... :-) I am not doing any progress at all with this problem. Really need help!(Saw that Steve had beed in similar thread 1,5 yr ago) Regarding resulting EMF/WMF shapes, there seems to be a big difference between seeting the Shape Border to Solid manually compared to doing it through VBA or through a Chart Style ( I took a standard one). This is bizarre! Is there some way to emulate the manual action in the menu? i.e. something like: Application.Commandbar...

Help setting up Charts
I have a spreadsheet with the following Column F, building name Each row on the spreadsheet is a member of staffs name In Column I, I have got the number 1 if the member of staff has been trained. If not there is a 0. I need a Chart to show the number of members of staff who are trained per work area. In the chart/ graph the work area should be along the bottom row X. The number of staff members will be in the Y column. Please would you be able to give some assistance. When I try to create a chart the information does not show and it appears to be cluttered. There are 91 members of s...

Hotbar removal
I got a colourful message from a friend and a link at the bottom which took me to Hotbar.com At no cost (suspicious) the visitor can download and install of an extension to MS Outlook, adding colours, stationeries etc. It was supposed to be compatible with MS Outlook and I had (na�ve) an impression that it had to be somehow endorsed by Microsoft. Immediately after the installation I saw that I got more than I bargained for. For one, it seems to be a spyware. It has also embedded itself quite deep and there is no uninstall option. What bugs me the most is that it has replaced my Copernic S...

Multiple criterias in a Pivot filter
Hi (Excel 2010) In my pivottable I have a list of names. Now I want to show only the rows with names including "ete" or "eve". I can set up af filter for each, but how do I filter for both of them. Furthermore I would like to show the rows with "Johnsson" Is there a way to do it. Something like : Select * from names where (names like %ete% or names like %eve%) and names like %Johnsson% Example: NAMES John Adams (Not to to be shown) Peter Grandjean (To be shown) Eric Hansen (Not to be shown) Evelyn Bond (To be shown) Regards Peter hi Peter, Set P...

HELP with an INSERT INTO statement
Hello everyone, I received the response below for a previous question. I understand the logic behind the reply, and I am sure it will work. I can't, however, figure out what I should be substituting for '2345' in the "SELECT '2345' AS" part of the solution below? I'm getting very close to running out of time and would greatly appreciate any assistance. Regards, Franc. Hi Franc, > > Use an append query. Probably you'll need to write VBA code to > assemble and execute the necessary SQL statement, which could look > something like this: > ...

Please Help,...Preview Pane
I am trying to change my settings so that, after I view an e-mail through the preview pane, the message header is not bold anymore after I move to the next message. However, when I go to Tools - Options - Other - Preview Pane, the dialog box with the correct option is locked to me. How do I unlock this box? Thanks Are you using a personal computer or one at work? It sounds like the admin has it locked down. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search En...