+ Reply to Thread
Results 1 to 2 of 2

pivot with primary axis 2 tables as input and secondary axis the same unit?

  1. #1
    Registered User
    Join Date
    03-19-2020
    Location
    Hasselt, Belgium
    MS-Off Ver
    office 365
    Posts
    2

    Question pivot with primary axis 2 tables as input and secondary axis the same unit?

    Hello,

    this is my first time on this forum, I hope this works out with the question I am introducing.

    I have the following situation where I visually want to report tracking of in and outs on projects.
    2 (or multiple) tabs or used at this point.
    On each tab I have a table (wich are individually defined as tables)
    for example: tab 1 Input with tblInput, tab 2 with tblOutput, tab 3 with tblBaseData

    The setup I have now is:
    1. tblBaseData has the main product data reference and is linked to tblInput and tblOutput so that there are links between the tables. (one-to-many relation)
    2. tblInput has a date column for amount and release date
    3. tblOutput has a date column for amount and release date
    What I want to do is use a pivot diagram to see the difference in In and Output in time (I do not know the term in English, stagnating or summate value in time)
    the issue I am running into is that the X-axis is the time axis and both tables have different dates included but I want to stick with one main axis (for example months).
    The Y-axis stays the same as being the amount.

    Could someone help me how I can solve this?
    Maybe a different approach is better; I am open to hear about this.

    Thanks in advance for your willingness to read this and to your answer.

    Regards, Johan

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,373

    Re: pivot with primary axis 2 tables as input and secondary axis the same unit?

    It is difficult to visualize exactly what you describe.

    A pivot chart cannot use a date axis. If a proper date axis is important to this project, you will either need to forego the pivot table/chart, or you will need to use a technique like this (https://peltiertech.com/regular-char...-pivot-tables/ ) to create a regular chart from a pivot table.

    In my work, I almost always prefer XY scatter charts to other charts. Having multiple series where each has its own Xvalues range is easy in an XY scatter chart. I would normally suggest that you use an XY scatter chart for something like this. The only downside to a scatter chart when you have dates along the x axis is that a scatter chart cannot divide the x axis up into even months (or quarters or years).

    If you need the nice date axis of a line chart, and the flexibility to have multiple series each with its own x values range, I would probably use a line + XY scatter combination chart. One series is a line chart and provides the nice date axis. The other series are XY scatter series.

    Does that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Primary Axis & secondary Axis have high gap to show in chart
    By leakhna in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-05-2018, 02:19 AM
  2. Primary and Secondary axis on pivot chart
    By sabha in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-13-2017, 01:29 PM
  3. Replies: 2
    Last Post: 10-08-2014, 08:04 PM
  4. Replies: 1
    Last Post: 10-08-2014, 06:36 PM
  5. Replies: 4
    Last Post: 05-17-2012, 11:25 AM
  6. Replies: 6
    Last Post: 12-07-2010, 02:54 PM

Tags for this Thread

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