Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have accounting transaction records, about 5000. I have them tagged by quarter, with the beginning balance being quarter 0. I'm trying to figure out how I can get the pt to show Q1= 0+1, giving a YTD Q1 ending balance, then show the Q2 net activity in a column and calcualte a Q2 ending balance , then show Q3 activity and calcualte a Q3 ending balance, and again for Q4. I tried grouping 0+1 ok, but then got stuck taking that and adding Q2 activity to get a Q2 ending balance, etc. Any ideas? TYIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Click anywhere in the data area.
    Select Pivot Table | Field Settings... from the Pivot table toolbar.
    Click Options>> to expand the dialog.
    Select "Running total in" from the "Show data as" dropdown.
    Select Quarter in the list of fields.
    Click OK.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='Torquemada' post='769103' date='03-Apr-2009 15:49']I have accounting transaction records, about 5000. I have them tagged by quarter, with the beginning balance being quarter 0. I'm trying to figure out how I can get the pt to show Q1= 0+1, giving a YTD Q1 ending balance, then show the Q2 net activity in a column and calcualte a Q2 ending balance , then show Q3 activity and calcualte a Q3 ending balance, and again for Q4. I tried grouping 0+1 ok, but then got stuck taking that and adding Q2 activity to get a Q2 ending balance, etc. Any ideas? TYIA[/quote]

    Why not try the following. Assuming that the Pivot Table is showing your tagged data as Columns.

    1. Generate the Pivot Table.
    2. Next while having the PV active.
    3. Select one of the Column Headings.
    4. On the PV toolbar Select Formulas - Calculated Item. A Dialog Box Should Open
    In the Dialog Box
    5 Change the Name to what you want such as "Cumulative Quarter 2" or "Q2 Cum."
    6. In the Formula after the =0 put in a + sign
    7 Next Highlight from the items listed on the right side the Item you want to have in the formula such as Quarter 1 and then Hit The Insert Item
    8. In the Formula put in another + sign
    9. Highligh the item you want such as Quarter 2 Then Hit Insert Item.
    10. Hit OK

    Your new item is now added to the PV and will add up Quarter 1 and Quarter 2.

    Repeat this process for Q3 and Q 4. With a Cum Q 3 as Q1 + Q2 + Q3 and Cum Q 4 as Q1 + Q2 + Q3 + Q4

    Regards,

    Tom Duthie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •