Search code examples
excelvbapivot-chart

Pivot chart (Excel) fails to render the data the way I want

I'm creating a piece of Java code to fill data into an excel file. The file is pre-created, inlcuding a pivot chart and some vba code.

Though the java code works, I have some problems getting the pivot chart itself to work. I've included a screenshot with some demodata. There are several Problems I'd like to solve:

  1. The X-Achis seems to behave like a category-axis, though it is formatted as a date-axis. Symptoms:
    • Dates are not ordered automatically, but only if I order the according column in the pivot-table
    • All dates have the same distance, though there is a gap of 1.5 years in the data
  2. The chartline of cathy has a gap. But i do want the line rendered, even if there are dates without data for cathy

Am I using the wrong chart, did I miss the an important checkbox in the chart options or do I really have to interpolate the data programmatically for every day before filling it into the table?

The last option would be not only be disappointing, but also distort the chart since I want to have the datapoints marked with symbols (like the squares and diamonds on the screenshot)

Any help is appreciated.

Example of my problem

Edit

By now this is my solution:

  • First of all: pivot charts are great, unless you want to visualize randomly ordered dates
  • I'm going to copy the pivot-data via vba into another sheet and use a regular chart
  • Gaps in the chartline can be avoided by using a point-diagram (X/Y-data) and selecting "connect datapoints with line" in the Dialog in "datasource > hidden and empty cells" (roughly translated from the german locale)
  • The vba-code will be extended to set up the diagram to my needs (adjusting range of input data and so on)

Still I'm kind of disappointed. I would have thought this was a usual usecase.

Solution

  • I set up trial workbook.

    1. I based a line chart on a range of data that had a column of dates.
    2. It was NOT pivot chart, just an ordinary one

    3. When I went to the x-axis formatting options and chose "Date axis" I was presented with choices about how to span the dates out (to do what you want).

    4. When I created a chart based on a pivot table using the same data, the x-axis formatting options did not give me the same choices

    It would seem that pivot charts do not allow dates to be spanned out.

    So you might like to consider basing your chart on a range of data not a pivot table.

    I also found this useful page, all about this sort of thing: here

    A chart based on a range Harvey