+ Reply to Thread
Results 1 to 5 of 5

Charting data against dates where dates are not at fixed intervals

  1. #1
    PK
    Guest

    Charting data against dates where dates are not at fixed intervals

    I have several data points that are the result of a pivot-table and I'm
    trying to plot them in a pivot-chart (I can easily get them out of the pivot
    table if the graph format is not supported for pivot-charts). Here's an
    example of the data:

    2005/01/15 2005/02/22 2005/03/18 2005/05/20
    X 16 18 22 12
    Y 4 6 3 2
    Z 14 19 12 34

    What I'd like to do is plot the data (preferably on a line chart) such that
    the dates are on the x-axis and spaced as they would be on a calendar and
    not simply as 4 points on a chart. e.g. in the example above there would be
    4 points per category but the points would be at varying intervals on the
    date axis as they are not equidistant in time. Is this possible?

    All help appreciated?

    Regards,
    PK




  2. #2
    Tushar Mehta
    Guest

    Re: Charting data against dates where dates are not at fixed intervals

    Did you try the default chart that XL creates based on your PivotTable?
    Once you have that, if it is not already a Line chart, change the type
    (select the chart, then Chart | Chart Type...). If the x-values are
    not correctly spaced, click the chart then Chart | Chart Options... |
    Axes tab. In there set the type of the x-axis to 'Time scale'.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <fM1re.313257$cg1.237772@bgtnsc04-
    news.ops.worldnet.att.net>, [email protected] says...
    > I have several data points that are the result of a pivot-table and I'm
    > trying to plot them in a pivot-chart (I can easily get them out of the pivot
    > table if the graph format is not supported for pivot-charts). Here's an
    > example of the data:
    >
    > 2005/01/15 2005/02/22 2005/03/18 2005/05/20
    > X 16 18 22 12
    > Y 4 6 3 2
    > Z 14 19 12 34
    >
    > What I'd like to do is plot the data (preferably on a line chart) such that
    > the dates are on the x-axis and spaced as they would be on a calendar and
    > not simply as 4 points on a chart. e.g. in the example above there would be
    > 4 points per category but the points would be at varying intervals on the
    > date axis as they are not equidistant in time. Is this possible?
    >
    > All help appreciated?
    >
    > Regards,
    > PK
    >
    >
    >
    >


  3. #3
    Jon Peltier
    Guest

    Re: Charting data against dates where dates are not at fixed intervals

    Unfortunately, one of the many limitations of pivot charts is that they
    only allow a purely categorical category axis, and no time-scale axis.

    You could include records in your pivot table source data range that
    include the missing dates but have blanks for the actual data. You'll
    have to right click on the date field header, choose Field Settings from
    the context menu, and check Show Items with No Data.

    Or you could make a regular chart from the pivot table:
    - select a blank cell which is not connected to the pivot table
    - start the chart wizard, choose the chart type in step 1
    - click on the Series tab in step 2, and enter the name and values for
    each series separately

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    Tushar Mehta wrote:

    > Did you try the default chart that XL creates based on your PivotTable?
    > Once you have that, if it is not already a Line chart, change the type
    > (select the chart, then Chart | Chart Type...). If the x-values are
    > not correctly spaced, click the chart then Chart | Chart Options... |
    > Axes tab. In there set the type of the x-axis to 'Time scale'.
    >


  4. #4
    PK
    Guest

    Re: Charting data against dates where dates are not at fixed intervals


    "Tushar Mehta" <[email protected]> wrote in message
    news:[email protected]...
    > Did you try the default chart that XL creates based on your PivotTable?
    > Once you have that, if it is not already a Line chart, change the type
    > (select the chart, then Chart | Chart Type...). If the x-values are
    > not correctly spaced, click the chart then Chart | Chart Options... |
    > Axes tab. In there set the type of the x-axis to 'Time scale'.


    Tushar, this did not work ... it changed all my dates from e.g. 1/26/2004 to
    1/1/1900 and the next date to 1/2/1900 (IIRC) - I think it just took the
    'category number' and made it a date.

    Regards,
    PK

    >
    > --
    > Regards,
    >
    > Tushar Mehta
    > www.tushar-mehta.com
    > Excel, PowerPoint, and VBA add-ins, tutorials
    > Custom MS Office productivity solutions
    >
    > In article <fM1re.313257$cg1.237772@bgtnsc04-
    > news.ops.worldnet.att.net>, [email protected] says...
    >> I have several data points that are the result of a pivot-table and I'm
    >> trying to plot them in a pivot-chart (I can easily get them out of the
    >> pivot
    >> table if the graph format is not supported for pivot-charts). Here's an
    >> example of the data:
    >>
    >> 2005/01/15 2005/02/22 2005/03/18 2005/05/20
    >> X 16 18 22 12
    >> Y 4 6 3 2
    >> Z 14 19 12 34
    >>
    >> What I'd like to do is plot the data (preferably on a line chart) such
    >> that
    >> the dates are on the x-axis and spaced as they would be on a calendar and
    >> not simply as 4 points on a chart. e.g. in the example above there would
    >> be
    >> 4 points per category but the points would be at varying intervals on the
    >> date axis as they are not equidistant in time. Is this possible?
    >>
    >> All help appreciated?
    >>
    >> Regards,
    >> PK
    >>
    >>
    >>
    >>




  5. #5
    PK
    Guest

    Re: Charting data against dates where dates are not at fixed intervals


    "Jon Peltier" <[email protected]> wrote in message
    news:[email protected]...
    > Unfortunately, one of the many limitations of pivot charts is that they
    > only allow a purely categorical category axis, and no time-scale axis.
    >
    > You could include records in your pivot table source data range that
    > include the missing dates but have blanks for the actual data. You'll have
    > to right click on the date field header, choose Field Settings from the
    > context menu, and check Show Items with No Data.
    >
    > Or you could make a regular chart from the pivot table:
    > - select a blank cell which is not connected to the pivot table
    > - start the chart wizard, choose the chart type in step 1
    > - click on the Series tab in step 2, and enter the name and values for
    > each series separately


    Thanks Jon, this is what I ended up doing and it looks pretty good ... now
    if only I knew how to set the default line width and backgrounds on all
    charts to something I can see more easily I'll be set. It's quite a lot of
    work to format ~30 charts changing backgrounds and increasing line width on
    >5 series per chart.


    Thanks again,
    PK

    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    >
    > Tushar Mehta wrote:
    >
    >> Did you try the default chart that XL creates based on your PivotTable?
    >> Once you have that, if it is not already a Line chart, change the type
    >> (select the chart, then Chart | Chart Type...). If the x-values are not
    >> correctly spaced, click the chart then Chart | Chart Options... | Axes
    >> tab. In there set the type of the x-axis to 'Time scale'.
    >>




+ 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