Bar Charts with Dates on X-Axis are Compressed

[BooMai]

Hi.

I’ve got some data in a pivot table. Two columns, a date and the count of activities on that date - a bit like

1/9/92 1
2/9/92 2
1/3/97 1
22/5/98 3

etc.

I want to plot these on a bar chart with the date running along the X axis and the activity count on the Y axis. I can do this by selectingthe Pivot table and inserting a bar chart, but all the activities are next to each other. What I want to see is the count of activities on 1/1/92, then 2/9/92, then there should be a gap of about 4.5 years until the next count on 1/3/97. Instead, the bar chart puts all the dates next to each other. Is there a way of having the elapsed date on the X-axis without having to do it mandrualically (i.e. I could create a table with all the dates from 1/9/91to whenever my data ends and then use countif on the source data. This would be a pain cos the activities are ongoing and also I don’t want to count all activities for a particular date, just the ones that meet a certain criteria. Pivot table does all that for me. I could do it myself, but would have to create and maintain a few extra colums in the source data which I’d rather not do.).

Thanks in advance.

[LeroyG]

@BooMai

a bar chart

Use an XY chart, or add all the empty dates between 92 and 97.

[BooMai]

Thanks, but I tried an XY chart, but it doesn’t really work because I can’t plot bars - only points (no good) or lines (worse).

Forgot to mention - the example data in my OP were just that;. The real data start in 92, are ongoing and will go on for many years.

[LeroyG]

[quote=“LeroyG, post:2, topic:107275”]
add all the empty dates between 92 and 97
[/quote]

So, no pivot table here.
If do you don’ t add borders to the columns, you can barely see them. And there is no apparent difference between 1992-09-01 and 1992-09-02.

BooMai_LeroyG.ods (48.2 KB)

[BooMai]

I don’t understand your post or your chart. Did you create it using empty cells for all the dates in between like your prior post or have you got a way of putting bars on an XY chart.

[LeroyG]

@BooMai

Did you create it using empty cells for all the dates

I did something silly. Sorry.
If the dates are numbers (not text), Calc automatically add the extra space, so there is no need to add all the dates that got no data.
BooMai_LeroyG.ods (23.6 KB)

EDIT: But Pivot Table charts behave differently. You can copy the data from the pivot table and use the pasted data to create the chart. Or reference the pivot table (=C20, etc.).
BooMai_LeroyG Pivot reference.ods (44.1 KB)

[BooMai]

I know how to do this in any number of ways by modifying, copying or manipulating the source data but I don’t want to. A pivot table does it with no effort from me without the risk of errors in modifying and maintaining a huge data set, particularly as I don’t enter the data - I just analyse it.

If the answer is that there’s no way to format a bar chart x-axis so that it treats dates as being on a timeline instead of treating them just as unrelated data points then that’s understood and I’ll live with the pivot table output.

Thanks anyway.

[LeroyG]

@LeroyG

reference the pivot table

With =IF function so empty cells don’t get 0.

[Villeroy]

There are lots of issues with pivot tables and pivot charts, and this is one of them.
PV_Chart_Scaling_Issue.ods (90.2 KB)
Source data on first sheet with data of January 2016 removed.
Pivot on second sheet. First chart is a pivot chart, second is an ordinary chart made from the pivot area. Both charts show the same issue.
Third sheet has a copy of the pivot area and a chart that shows a numeric time scale on the x-axis.