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.
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
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.
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,
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.