November 17, 2009, 10:24 AM
StefanAGA: Problem with Line/Bar Graph and missing values
Hi, following problem:
I want to show 2 values in a graph over one year.
First value is given not daily, but for every 2-3 days.
This value I want to show with a line graph.
Second value is given every 6-8 weeks. This value I want to show with a bar.
Note:
- There are days with no entry.
- Second value can exist for a day even if no first value exists.
To prevent that the width of the bar changes depending on the number of occurances, I thought of the following:
On ther X-Axis I group the data by the last 365 days, eg. 2008-11-18 to 2009-11-17.
This solves my problem with the width of the bar. Great.
But therefore my line graph "touches" the x-axis when it has no values; now the graph looks like a rollercoaster ;-)
I would like my line graph only to connects those fields with present values.
I read about something like "suppress zero lines", but it seems as it doesn't work with my line graph.
November 17, 2009, 04:12 PM
WazIt seems that htere is something that does this.
Its to do with missing data.
Search for GRAPH INTERPOLATE
I think that the graph style added is setFillMissingData(n);
I could not get it to work though.
November 18, 2009, 07:45 AM
StefanHi Waz,
I tried
setFillMissingData(n)
in combintation with some
MSSING
settings. This seems to be nice.
But, as far as I could see,
setFillMissingData(n)
works for all series. I want to use it just for one of my series.
November 18, 2009, 03:33 PM
WazIf no one else has any suggestions, then I would suggest working out the values your self prior to graphing.
November 19, 2009, 02:20 AM
StefanSo here is what I did:
1.) Create a HOLD-File H_DATES with the last 365 dates as
CALDAY_NAME
2.) Create a HOLD-File H_LINES with the values for the line series with
MISSING ON
and
BY NAME
3.) Create a Hold-File H_BARS with the values for the bar series with
MISSING ON
and
BY NAME
4.) Match the files from Step 2 and 3. The Option
OLD-OR-NEW
ensures that I get all the data from both files, even with I do not have a corresponding date in on of them.
MATCH FILE H_LINES
PRINT LINEPRICE
BY CALDAY_NAME
RUN
FILE H_BARS
PRINT BARPRICE
CALDAY_NAME
AFTER MATCH HOLD AS H_GRAPH OLD-OR-NEW
END
-RUN
5.)Join the files from Step 1 and Step 4 to build my X-axis:
JOIN
LEFT_OUTER H_DATES.CALDAY_NAME IN DATES TO MULTIPLE
H_GRAPH.CALDAY_NAME IN H_GRAPH TAG J4 AS J4
END
6.)Build a last HOLD-File to prepare the data for my graph:
TABLE FILE H_DATES
PRINT
J4.H_GRAPH.LINEPRICE
J4.H_GRAPH.BARPRICE
BY H_DATES.CALDAY_NAME
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS H_GRAPH_2 FORMAT FOCUS
END
7.) Give a Define on Step 6, as I want to show Month and Year on the y-axis
DEFINE FILE H_GRAPH_2
YEAR/A4 = EDIT (CALDAY_NAME, '9999$$$$');
MONTH/A2 = EDIT (CALDAY_NAME, '$$$$99$$');
END
8.) Create Graph
GRAPH FILE H_GRAPH_2
-* Created by Advanced Graph Assistant
SUM
LINEPRICE
BARPRICE
BY YEAR
BY MONTH
BY CALDAY_NAME NOPRINT
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
...
I figured out that ON GRAPH SETSTYLE, following options had to be set:
ON GRAPH SET GRAPHSTYLE *
...
setFillMissingData(2);
setNumMissingDataSegments(25);
setScaleFromZero(false);
setRiserWidth(50);
...
I'm still working on one thing: I want to display the labels for BARPRICE. Unfortunately - I think due to the interpolation with setFillMissingData(2) - the labels for BARPRICE are even shown for the interpolated values. Maybe I can figure this out too.
Thanks all for your help.