stacked area chart not ingnoring #NA values

24 views
Skip to first unread message

Ion Luc

unread,
Oct 27, 2017, 10:45:07 AM10/27/17
to Google Visualization API

Hello everyone,

I have the following question: what is a way to have a stacked area chart only show the non-zero values? 

Below it's the table I'm using ( the cells that display #N/A have the formula: =NA(), but also tried with other values 0, EMPTY )

DateClosed (SP)Open (SP)
17-09-270293
17-10-0410371
17-10-1118415
17-10-1818435
17-10-2555404
17-11-01#N/A#N/A
17-11-08#N/A#N/A
17-11-15#N/A#N/A
17-11-22#N/A#N/A
17-11-29#N/A#N/A
17-12-06#N/A#N/A
17-12-13#N/A#N/A
17-12-20#N/A#N/A
17-12-27#N/A#N/A
18-01-03#N/A#N/A

And the chart displays as below:


I would like that the values shown stop at 17-10-25 (which is the current date) and use this chart to generate a trendline that would show where we can get at by 18-01-03. 

Thank you,
Ion Luc

Daniel LaLiberte

unread,
Oct 27, 2017, 11:47:51 AM10/27/17
to Google Visualization API
Hi Ion,

You'll need to do a couple things, but the first is probably enough.  Add to your options: hAxis: { viewWindow: { max: 5 } }.  5 is the index of the next row after the last day with data.
If you don't know which row that is going to be, then consider the next thing: change your date column from strings to dates (using the format option in sheets), and then you can specify the viewWindow.max with today's date.  

Finally, to avoid drawing dots or connecting lines for any non values, no matter where they might show up, make sure you are specifying null, undefined, or NaN values rather than strings, event empty strings, since an empty string is treated as a 0 value.  This won't hide all the trailing rows that have no data, however.  For that, you will still need the above.

It would be easier for us to help you if you could show us the actual code you are using, including the data and options.  Can you point us at your web page?  Here is a jsfiddle showing how these various special values behave: https://jsfiddle.net/dlaliberte/tkzhgfu7/


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/529c6c3a-0d2e-42f0-9c59-bbd9ca4855e1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--

Ion Luc

unread,
Oct 27, 2017, 11:57:17 AM10/27/17
to Google Visualization API
Thank you for your answer. 
I'm actually doing this in a googlespreadsheet document, not by code, and would like to know what setting can I use to have it displayed properly:

Here you have a link the document:
https://docs.google.com/spreadsheets/d/1wjz_qX61jfFyMd9Feey8iZBHP73SEMBMDAcHq5cux3k/edit?usp=sharing  

I would like all the horizontal values (dates ) to appear on the chart (that is why I'm using the "Treat Labels as Text" box checked -> otherwise it is only showing a value per month on the horizontal axis, and I really need to see them all)

To post to this group, send email to google-visua...@googlegroups.com.



--

Daniel LaLiberte

unread,
Oct 27, 2017, 12:11:06 PM10/27/17
to Google Visualization API
You'll have to ask in a forum about Sheets for questions about how to do things (if they are possible at all) in Sheets.  

You might need to use AppsScripts to get enough programmatic control of the charts in Sheets.

To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsubscr...@googlegroups.com.



--

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.

Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/f38cc158-6a41-4ede-b009-538fc01f1a3f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--

Ion Luc

unread,
Oct 27, 2017, 1:58:53 PM10/27/17
to Google Visualization API
Thank you, appreciate the quick feedback! 

Have an excellent evening,
Ion
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.



--

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.



--
Reply all
Reply to author
Forward
0 new messages