Null values in chart

[hodankr]

Hello,

I have a chart where any data that is null is showing as a N/A when exported to excel.  For example, Jan has 1, Feb 0, Mar 2, Apr 0 and May 0.  For the Months of Feb, Apr and May, I am seeing N/A on those bars where I don't want to see anything.  Has anyone ever run into this before?  I tried suppression, but that does not work.  I am using 8.4 and exporting to excel 2003.

Thanks.

[tupac_rd]

does this help
http://www-01.ibm.com/support/docview.wss?rs=3442&context=SS9RTN&q1=%2bnulls+%2bin+%2bexcel&uid=swg21417814&loc=en_US&cs=utf-8&cc=us&lang=all

[hodankr]

Thank you for your help, but no it did not work.  I'm not sure if it has something to do with the way I manipulated the date field.  I extracted the month out of the date field, but then had to create the following calc to get it to present the correct way in the graph:

If ([Planned Start Month] = 1) then ('Jan') else
if ([Planned Start Month] = 2) then ('Feb') else
if ([Planned Start Month] = 3) then ('Mar') else
if ([Planned Start Month] = 4) then ('Apr') else
if ([Planned Start Month] = 5) then ('May') else
if ([Planned Start Month] = 6) then ('Jun') else
if ([Planned Start Month] = 7) then ('Jul') else
if ([Planned Start Month] = 8) then ('Aug') else
if ([Planned Start Month] = 9) then ('Sep') else
if ([Planned Start Month] = 10) then ('Oct') else
if ([Planned Start Month] = 11) then ('Nov') else
('Dec')

I may be heading in the wrong direction with that, but not sure how to fix.


This is what it shows then when I look at the series in excel:  ={69,100,28,#N/A,#N/A}  There are no values for Apr and May, but they are listed in the series values.  I just need them to not be there.  I don't even need it to say 0, but not show at all.

Thank You.

[fdiang]

Probably not the best solution but if you are using a relational datasource, maybe breaking down into two querys can take the job done. Once you have the data, you do the decoding (month names).

Query_1 <- Query_2

Hope that helps,

[wasted_c]

Are you using Count or Total?

one approach i did use in the past was:

- manipulate the month or period as you did.
- use a case statement to identify the month.
- new per month data items, using an If statement to get (1) or (0) based on the actual month.
- new per month data items, using total to get the total planned per month.