Grouping daily transactions by month (using pivot tables), across years

Hello - Could someone please let me know the best way to use a pivot
table to group daily transactions into monthly totals.

I noticed there is a "Group and Show Detail" option----but it seems to
be unable to portray 2 separate Januarys when they fall into two
separate years.

For instance, if my data is

1/1/07      1000
1/4/07      8000
1/19/08    10000

I just get a total for January, rather than two separate totals for
Jan 07 and Jan 08.

Is there a way to allow the pivot tables to show Monthly totals, but
ensure that I can distinguish between years?
Is there possibly a way to include the same "Date" field twice, and
then group the first field by year and the next field by month?

I realize I can accomplish this with the sumproduct function......but
I would prefer to build it into the pivot table if at all possible.

Thanks for any suggestions.
0
9/8/2008 9:01:28 PM
excel 39879 articles. 2 followers. Follow

1 Replies
493 Views

Similar Articles

[PageSpeed] 59

You will need to group by years AND by months so as to differentiate the two 
Jans.

-- 
__________________________________
HTH

Bob

"Dave K" <fred.sheriff@gmail.com> wrote in message 
news:06c35003-cf1f-419a-aab0-9da6bd8b4775@p25g2000hsf.googlegroups.com...
> Hello - Could someone please let me know the best way to use a pivot
> table to group daily transactions into monthly totals.
>
> I noticed there is a "Group and Show Detail" option----but it seems to
> be unable to portray 2 separate Januarys when they fall into two
> separate years.
>
> For instance, if my data is
>
> 1/1/07      1000
> 1/4/07      8000
> 1/19/08    10000
>
> I just get a total for January, rather than two separate totals for
> Jan 07 and Jan 08.
>
> Is there a way to allow the pivot tables to show Monthly totals, but
> ensure that I can distinguish between years?
> Is there possibly a way to include the same "Date" field twice, and
> then group the first field by year and the next field by month?
>
> I realize I can accomplish this with the sumproduct function......but
> I would prefer to build it into the pivot table if at all possible.
>
> Thanks for any suggestions. 


0
BobNGs (423)
9/8/2008 9:19:31 PM
Reply:

Similar Artilces:

Pivot Tables
We are using a pivot table to analyze some data we have. Currently we are showing the client then the product and then the sum of the Number of orders and the percentage of margin. The table looks like this: Client A Product1 Sum of ordernumber Sum of MarginPercent Product2 Sum of ordernumber Sum of MarginPercent Is is possible to have the data shown as: Client A Product1 Sum of ordernumber Sumof MarginPercent Product2 Sum of ordernumber Sumof MarginPercent In the pivot table, d...

Month Filter
I keep a log of checks that I receive in my department throughout the year. I have the checks listed by date (mm/dd/yy), payee, check #, etc. What I want to do is be able to filter the list by month (ex., March 2005). Is there a way to do this? Thanks Joseph You can Try EasyFilter Joseph http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Joseph" <joe@nospam.com> wrote in message news:%23lQNnTawFHA.464@TK2MSFTNGP15.phx.gbl... >I keep a log of checks that I receive in my department throughout the year. I have the checks lis...

GP 9.0 Table descriptions
Is there any way to find the descriptions for each table in GP? Thanks, Tony Absolutely! Go to Tools >> Resource Descriptions >> Tables. Every table is listed with all the fields. You should also consult the SDK that is on the second GP cd. Additionally, Richard Whaley has published a series of books at Accolades Publishing that does a thorough job of documenting the tables, much better than what MS offers. -- Charles Allen, MVP "Tony" wrote: > Is there any way to find the descriptions for each table in GP? > > Thanks, > Tony > > &g...

Global Fields in SQL tables
Does anyone know where do I find the global fields that are available in Report writer in what SQL database table? I need to know cause I need some of the global found in the Report Writer to insert them into a crystal report. Thanks in advance That depends on what field you are looking for. Which fields do you need? "ec" wrote: > Does anyone know where do I find the global fields that are available in > Report writer in what SQL database table? I need to know cause I need some of > the global found in the Report Writer to insert them into a crystal report. > &g...

Accept transactions for Bills with bank amounts vs. estimated amou
I have a "bill" set up for my recurring paychecks (deposits) and actual bills, like electricity. In all cases, I have the amounts marked as estimates and have items automatically entered into the register zero days before the payment date. Now, when I download transactions, Money correctly matches the downloaded deposit with these automatically entered "bills", but it is using my estimated amounts instead of the downloaded amounts I don't recall having this problem in 2002 (upgraded from 2002 to 2006 recently). Is it possible to take the downloaded amount inst...

Using wildcards w/ sumproduct
I'm not familiar with wildcards in formulas, and I can't seem to figure out exactly what to do here. If the first 3 letters in cells of column A contain FPC, then sum corresponding rows in column P, then by the total number of cells in column AT where the first 3 letters are FPC. =SUMPRODUCT((LEFT(A1:A20014,3*(closed!$A$3:$A$20003="FPC"))),(closed!$P$3:$P$20003)/SUMPRODUCT((LEFT(A1:A20014,3*(closed!$AT$3:$AT$20003="FPC"))))) Any assistance is greatly appreciated. Thanx, ~Julz Hi if I understood you correctly try =SUMPRODUCT(--(LEFT(closed!$A$3:$A$20003)=&qu...

Manufacturing Table Locks
All / Gerald Do you know which tables contain the locking information for MO and PO in the manufacturing module. Regards Darren Bowen There are a DexLock and DexSession tables in the TempDB. However, Dexterity is designed to use passive locking for most of the time which means that it does not actually apply locks at the DB level except for the first milliseconds it actually updates the data. David Musgrave Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@nospam-microsoft.com Any views...

How can I find out which apps are using .NET and which not?
I appear to have three versions of .NET (2, 3, 3.5) with their associated special packs. How can I find out which apps are using .NET. (Please, no 'cut-one-out' method, I do not want to uninstall and see what has stopped working.) I am aware that the different .NETS are individually and separately usable by different apps - my questions is what app uses which? Thanks for any pointers occam wrote: > I appear to have three versions of .NET (2, 3, 3.5) with their > associated special packs. > > How can I find out which apps are using .NET. (Please, no 'c...

Outlook using RPC over HTTP and Passwords
We have implemented mandatory password changes every 60 days and the remote users with Outlook over http and not given an opportunity to change the password when it expires. After the 60 days they can't get in and I need to change their password on the server. Is there something I need to do? or fix? VPN users are being prompted to change their passwords and can change them themselves. -- Regards Chris Can they change them from OWA? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/...

Exchange 2007s administrative group restore?
Greetings, I was installing ex2007 and the uninstalled it. I saw the administrative group in adsiedit which it left behind (which I deleted) I know, it was a bad move now I cannot reinstall ex2007 as a hub-transport & edge transport server/role How can I reclaim the 'remnants' (ex2007 admin/routing/other groups) which the previous uninstall left - so the ex2007 install works? Thanks, Mike Bonvie I figured it out.... by running the following at the command-line in the exchange2007 install folder, the objects are regenerated (similiar to forest/domainprep in 2003) setup ...

Using the Google Calendar .net API from an Access application
I have a scheduling/calendar application in access. I also use Goggle calendar. Would it be possible to use .net from Access to send my schedular entries to my Goggle calendar? OP <OP@discussions.microsoft.com> wrote: >I have a scheduling/calendar application in access. I also use Goggle >calendar. Would it be possible to use .net from Access to send my schedular >entries to my Goggle calendar? It might be possible but you'd have to do a lot of digging through the Google calendar documentation looking for the various APIs and figuring out how to execu...

UPDATE Table with multiple textboxes
I have three unbound textboxes on a form. Each textbox is used to input the amount of money A,B,C deposited. textbox1 is labeled A, textbox2 labeled B,.... When the user clicks a button, I need to update the table to add a row for A then add a new row for B and then C. Below is the code I have so far for the update, but I can't figure out how to repeat the update for B & C. Lastly, the table has a field named 'memo'. I would like to add the textbox caption to that field on each record. So, for the first record, the deposit field would be whatever num...

We should be able to enter credit memos as receiving transactions
Credit memos cannot be entered in the receiving transaction entry screen. These transactions should behave similarly to payables transactions. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-...

Pivot Table Grand Totals not appearing
I have calculated fields in my pivot table that work great. But if one of those calculated fields have nothing to calculate within the pivot table, the Grand Total does not display a viewable result. The Grand Total for that calculated field returns a 'blank'. As soon as I enter data in the source data to compliment the calculation... the Grand Total for that calcucaltion works perfect. Is there a switch or setting I can set in Table Options to make this work or are there any trick workarounds? Thanks in advance, Joe ...

FWD: Use pack
--nozugocqswhapz Content-Type: multipart/related; boundary="ocmbostjnqamcd"; type="multipart/alternative" --ocmbostjnqamcd Content-Type: multipart/alternative; boundary="wtcazseafbqr" --wtcazseafbqr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to prote...

Month mmmm format
I was setting up a calendar in Excel, and in Cell A1 had a manually entered date of 10/1/03. In cell C1, I entered the formula =MONTH(A1), which naturally returned a "10". I then formatted cell C1 as Custom: mmmm and was very surprised to see it return January when I expected it to return October (10th month). I tried to Google this, and I also went to Chip Pearson's website, but either I am searching on the wrong key words, or it hasn't been addressed. Is there anyone out there who can explain why it didn't work? I did create a lookup table to get around the issue, ...

Using a form instead of a parameter query.
We are using Access 2007. There are several parameters that my db users have to input to "filter" the data that will complete reports for the agency. I have no trouble using date fields on a form that allows the user to define the date range. However, the users need to specify foreign key fields (long int.) and I wish them to enter more than one value in the field at one time. Using the In () operator works fine on the query itself, but how can I have the users type several integers in a text box on a form that will form the parameter for the query? Any ideas? And than...

Is there a Print Preview without using the Preview app?
Is there a way to see what a document will look like on a printed page while working within Office? I know I can do Print>Preview, but that views the document in Preview, not in Office. "Office" is a suite of applications programs - knowing which of those programs you're referring to would make it a bit easier to answer your question:) In general, however, Office never has done the printing & the current Apple guidelines for printing allow for the apps to no longer have to provide a preview service which was [and is] generated by the OS & printer driver in the first ...

Week, monthly, quarterly
I have a table called as Task; this table has four columns Name, Task, Frequency and Week. Name: employee name Task: Assigned work to each employee Frequency and Week: the due day to perform the assigned task I would like to create a Form, when the Name is selected in a combo box and if any task is pending on that particular day it should be viewed For example: if today is Monday then if I select Name OP , in his list the pending task should be shown as B. Frequency column: Daily: if I have mentioned daily for any task then it should be viewed on everyday as pending task. ...

How to Copy Charts and Tables from Excel to Publisher
Hi, Everyone: I'm a new user of Publisher 2003, and I'm working on a 100-page or so document with text, photos, charts, and tables. I would appreciate advice about copying charts and tables from an Excel workbook and reducing file size. Please also recommend which is the correct file format to use when saving charts, tables, and photos as "pictures". My Excel workbook has numerous worksheets. (1) When I try to "copy and paste" a single Excel chart from one worksheet into Publisher, I get an "object" which shows ALL the worksheets if I double...

Investment Transactions not appearing in Cash Transactions
The Cash Transactions register is missing items that are present in the Investment Transactions register, causing the month-end money balance to not reconcile with the monthly statement from my brokerage. I'd like to try deleting all my transactions back through the most recently reconciled month, and downloading them all again. Is there any way to do that? Version: Money2001 Deluxe & Business thank you, /Jim What kind of items are missing? Buys and Sells? -- Michael Gordon MVP "Jim F" <hedgefund2020@comcast.net> wrote in message news:694601c3e668$b32bb8...

Problem with TOC using various TOC styles in the same table (Wrd20
Hello, I'm having a weird problem with the table of contents. I have been editing a long document and am regularly updating the TOC. I have just noticed that the spaces between the lines were various in the TOC and thus used the style inspector to find out why. It appeared that TOC 1, TOC 2, TOC 3, TOC 4...styles were all there in the TOC. I have no idea why, I'm creating the TOC repeatedly, removing or replacing the previous one and when creating, I'm choosing TOC 1 as the style but when the TOC is inserted, the problem is the same. Single space here, 1.5 space th...

Corrupt emails when using multipart content types
I am running Exchange 2003 with latest SPs and am running into a very strange problem. When we receive certain emails, the contents of the messages have been corrupted. Exclamation points (!) seem to appear randomly in the text (usually an exclamation point followed by a space). The messages both contain HTML and TEXT content, the header is: Content-Type: multipart/alternative; boundary="----=_NextPart_000_365D_01C59758.21CA9D10" The text version content type is: ------=_NextPart_000_365D_01C59758.21CA9D10 Content-Type: text/plain; charset="iso-8859-1" Content-Tr...

summary values across worksheets within the same workbook
I have a single workbook consisting of 53 sheets (one per week in the fiscal year). I have a summary sheet that I need to extract data from the same cell in every sheet, updated automatically when any sheet is updated. eg, Each of the 53 worksheets within the workbook has a total (car mileage) in cell C34, which I need to summarise as a total in a cell on the summary worksheet, on the basis of cell C34 on Sheet1+Sheet2+Sheet3... +Sheet53. What function do I need in the required cell on the Summary sheet, please, to meet my needs? I can't seem able to do what I want (I...

Layout of table
I have a table in excel, all of the information in this table is linked to AutoCAD. I have added an example below: Example 1 NO. NAME BOTTOM BOTTOM TOP TOP MAT FIN MAT FIN 101 CORRIDOR PLASTIC SMOOTH RUBBER SMOOTH 102 CORRIDOR PLASTIC SMOOTH RUBBER SMOOTH I am trying to get Example 1 to look like Example 2 without having to re-enter everything. It is the same info. but a different layout. I tried a Pivot Table but was not successful. Does anyone have any ideas? Example 2 NO. NAME BOTTOM TOP 101...