Pivot chart ignoring zeros

[jtkjames]

Hi everyone,

Just wondering if anyone on here knows a way to get Pivot Charts generated in Access XP to "ignore" zero values.

My situation is this - I have a pivot chart (line) with an X axis going back 1 year by month. I am plotting the prices of particular models of car over the year, but sometimes a model ends production mid year, and another may start mid year. What I want is the line to "start" and "stop" where the nonzero points end, which may be in the middle of the graph.

In Excel, AFAIK, you can just set the values to be ignored to be #N/A. But in Access, if I try this in a table, it does not recognise it as a valid number (which is understandable really).

Any ideas? I have tried other forums to see if any users on those have any suggestions but so far to no avail - also tried Googling but all results appear to be about Excel not Access

Thanks

James

 

[jtkjames]

I take it nobody has a solution for this?

 

[vbaInet]

I don't use Access' pivot charts much but let's see some screen shots of the data and the chart.

 

[jtkjames]

Hi vbaInet

Attached are the two images, one of my table of data and one of a pivot chart. The subject is cars. I want the Mercedes Benz line on the graph to stop at March 2010 rather than jump down to zero. Even when the table cells are empty, they are still interpreted as zero by the PivotChart and there does not appear to be any option to "ignore blanks" or similar...

Surely I'm not the only person in the world to have this issue? A search on Google returns no insights..

Anyway thanks for any help!

 

[vbaInet]

I think the chart starts plotting from left-to-right, rather than from right-to-left so what you want it to do is to start plotting from non Null values.

Can you post a stripped-down version of your db and if I've got a few moments I will have a look?

 

[jtkjames]

Attached is a simple table with the data in. I have made a query, taking all data from the table, and default viewing as a pivot chart.

As you can see, the blank spaces for the mercedes model in the table (required since that exact model of mercedes wasn't made back then) give a 0% figure on the chart, rather than just stopping the series abruptly in the middle of the chart (which is the correct way to show the data)

However I do want the whole 12 months for the rest of the car models, all on the same chart. Excel can do this with a #N/A statement instead of a zero, which tells the Excel Chart to not plot that point. Is there an access way to do it?

Thanks again

 

[jtkjames]

Anyone with any ideas? If this isn't possible in Access, can anyone suggest a way to use Excel's charting to automatically produce a chart - I can write a macro to export to an Excel table, but not to invoke an Excel chart from the table...

Alternatively can anyone please confirm if it is possible to make a chart that stops mid way in a later version of Access (I have XP but am willing to upgrade to 2010)

Thanks all!