Results 1 to 9 of 9
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table-YTD (Excel 2002)

    I don't understand. In your pivot table, Sum of Amount for MAR07 is -10,000 for Sales, and +7,000 for CoGS.
    But on your "desired layout" sheet, both amounts are positive. Can you explain?

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Table-YTD (Excel 2002)

    Hi!
    I'm wanting to do more with pivot tables in regards to YTD amounts... I understand how the running total works, however I'm interested in a slightly different format. I've attached an example to show what I'm wanting the report to show, but I'd like the pivot table to do it, as opposed to formulas. I'm wondering if this layout is possible in a pivot table... maybe I need to change the layout of my database that the pivot table reads?? Any ideas would be greatly appreciated.
    Thanks!!
    Lana
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table-YTD (Excel 2002)

    Oops... sorry about that Hans. The sales on my desired layout should be negative (denoting a credit balance). I've made the correction on the original worksheet.
    Thanks,
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table-YTD (Excel 2002)

    You can use formulas that look up values in the pivot table, or directly in the data table, but I don't think you can make the pivot table look like your "needed layout".

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table-YTD (Excel 2002)

    Hi Hans,
    This is bummer... these days I've been thinking anything is pretty much possible (I love Excel & macros!!), so it almost hurts when I hear it won't work [img]/forums/images/smilies/ohmy.gif[/img]) My goal is to have the general ledger data dump into Access, then have pivot tables in Excel read the Access database. The pivot tables would be in the form of financial statements (as shown in the worksheet example), and then if someone wanted to drill down to the detail they could double click the number and see the detail... this is why I want to use pivot tables as opposed to formulas. I've been thinking & trying different database layouts to try and get the pivot table to generate the Month and the YTD info, but to no avail. If anyone has done anything similar, I'd love to hear how they went about it.
    Thanks for your help Hans!
    Lana

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table-YTD (Excel 2002)

    Yahoo... I think I got it... for others that may be interested in the solution, I used a data layout that I normally wouldn't use for data I want to display in a pivot table (see attached file). I then copied the data, and pasted it right next to the original data (so it would appear TWICE in the data worksheet-of course, later in my project I plan on using a macro to copy it). I had to duplicate the data because the pivot table wouldn't let me perform a calculated item (the gross margin) if I used a field TWICE in the data area of the pivot table. I'm thinking I'm on my way to my end goal!
    Lana
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table-YTD (Excel 2002)

    OK, but that means your data are more or less in the desired format already. How difficult will that be to achieve in the real situation?

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table-YTD (Excel 2002)

    My example is very small compared to the real data. Now I'm thinking I may not need to dump it into Access, as maybe in it's new format it may be less then the 65,536 line limitation for Excel. I'm dumping the data from an AS/400, and I can get it to dump in the format needed... I think... if not, then I can have a pivot table switch it around to be in the format I need, which I can do thru a macro. So I'm still thinking it might be a viable option, maybe. One problem is the drill down function... when I double click on an amount, it practically gives the whole database, and not just the info related to that one number. I don't like that part. I'll keep working on it. I'm sure Woody's Lounge will be hearing from me if it doesn't pan out [img]/forums/images/smilies/ohmy.gif[/img]).
    Thanks Hans!
    Lana

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table-YTD (Excel 2002)

    Okay, this time I got it to work without having to change the database around. The answer was so obvious... I hate when that happens. Again, for those who might be interested in the solution, I've attached an example. In a nutshell, for the YTD columns, I simply created a calculated item on the "period" field. Then hide all the months, except MAR07, MAR06, MAR07YTD and MAR06YTD. Granted, double clicking to see the detail on the YTD dollar amounts doesn't work (because it's a calculated field) but I can live with that. The database layout remains the same, except having to have the dollar amount column show up TWICE... because I plan to use calculated items in my "row" area (gross profit), a pivot table will not allow you to use the same field (amount) twice in the "data" area.
    Lana
    Attached Files Attached Files

Posting Permissions

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