+ Reply to Thread
Results 1 to 7 of 7

Pivot Table displaying seperate values in one table?

  1. #1
    Registered User
    Join Date
    02-01-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Pivot Table displaying seperate values in one table?

    Hey guys,

    For work, I'm currently trying to stitch together a Pivot-Table in Google Sheets concerning start- and enddates of contracts and I've run into an impasse (see appended example). Since I tried the same Pivot-Table at home with MS-Excel 2016 and had the same errors, I know it's just my personal stupidity at play here, not the programs used.

    I have two main problems with my table:
    1. Is there some way to combine both tables and show starters and stoppers in every calendarweek in a single pivot table? Every time I've tried (and I tried a lot these past days >_<), it always displayed the starters twice, once in the starters and once in the stoppers row/column, and did not take ending contracts into account at all. (See screenshots)
    2. Is there a way to permanently bind the summarizing formula at the bottom to the sums of both tables? So that no matter how large both tables got the formula would still always aim at the right cells instead of being fixed to "B21+C8-C18", for example? (It does that with normal formulas, but somehow the summarizing formula ignores the growth of the pivot table when a new company is added in the source sheet and stays fixed to the same cell).

    Keep in mind that google-sheets is quite a bit crappier than Google sheets, so I had to put in filler-dates to show all Calendarweeks of the year. Just disregard those rows.

    I would be eternally grateful if someone could help me, cause god knows I've been breaking my brain into pieces trying to figure this stuff out. :-O


    Source sheet:
    Pivot-Example Source Material.JPG


    Seperate (working) tables I want to combine into a single one:
    Pivot-Example seperate tables.JPG


    My 30th try of a combined table. As you can see, it shows the same value in both rows (starters and stoppers). How do I get it to display both starters and enders for every company for every calendarweek?
    Pivot-Example combined table.JPG
    Last edited by Zelles; 02-11-2019 at 08:23 AM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: Pivot Table displaying seperate values in one table?

    Hello,

    can you post a small sample of the source data, then mock up the desired result manually and explain the logic that leads to that result. With pivot tables, the data structure of the source matters. Post that structure.

  3. #3
    Registered User
    Join Date
    02-01-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Re: Pivot Table displaying seperate values in one table?

    Sure I can :-) Thank you for your reply.

    I appended the example-table. In the first table (source data), there are 3 companies (AAAA, BBBB and CCCC) and "Last Year".
    They all have starting contracts and ending contracts in different calendarweeks of 2019 except for "Last Year", which are the contracts that started last year and are going to end in calendarweek 10.

    What I want the pivot-Table to do is (as you can see on the second sheet) display the whole year in calendarweeks and arrange the starting and ending contracts into that display. But everytime I try that, the pivot-table only takes the values from the start-weeks and displays it twice - once in "starters" and once in "Stoppers", so that for example in calendarweek 3 for Company AAAA it would say starters 1, Stoppers 1 instead of the 1&2 it should display.

    Is there some way to accomplish this? As I said, I guess it's got something to do with what I set the columns/rows/values to display. I just can't wrap my head around what option could theoretically lead to my desired result.
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: Pivot Table displaying seperate values in one table?

    Sorry, I was asking for the source data for the pivot table. You have posted some data that is separated by an empty column, so, clearly that is not the data you use for the pivot tables in your screenshot. Are they really pivot tables??

    Where does the text "Starters" and "Stoppers" come from if it's not in your data source?

    Maybe this is not a pivot table at all?

  5. #5
    Registered User
    Join Date
    02-01-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Re: Pivot Table displaying seperate values in one table?

    I... I thought I was supposed to mock up the desired result manually, so I didn't make another non-functioning pivot table but rather built together a manual depiction of what I want the table to do. It thought that was what you wanted?
    The source data is the same sheet I used in the examples in post 1. I don't know what to tell you. The source sheet in post 1 also had an empty column between "Start"- and "End-calendarweek". I merely deleted the "Filler"-Company because it's not really needed to show what I want.

    I double clicked the "Start-calendarweek" and "End-calendarweek" cells and renamed them to increase visibility. I assure you that the tables in Post #1 are Pivot-Tables. They draw data from the source sheet and automatically use it to display what I don't want to see. That's the whole problem^^

    I just want to combine both Tables in Screenshot two; the result should be a single, big table that shows starters and stoppers for each calendarweek, seperated by company. Is that possible or can pivot-tables only ever use a single value per table?

    Thank you for putting up with me - maybe I just did not understand what you need to help me :-/

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: Pivot Table displaying seperate values in one table?

    To build your layout in a pivot table, your source data needs to be a flat table like in this screenshot:

    2019-02-07_08-56-32.png

  7. #7
    Registered User
    Join Date
    02-01-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Re: Pivot Table displaying seperate values in one table?

    Ahhh, thank you - problem solved :-D

    Problem=User, I guess.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1