How can i get a MAX, MIN and a Avg line in a bar chart

[Merin Joseph]

[Patrick Huebgen]

Did you looked into the partition aggregation functions?

[Merin Joseph]

No im looking for how can i add those 3 aggregated functions as trend line in a bar chart. Please refer the above attachment. Thank you

[Patrick Huebgen]

Yes - I know. But I think that you are not looking for a trend but an aggregation on the overall level of data. this could be done using partition aggregation - just create compute fields and use the function to build your calculations as described here

https://ecl.informationbuilders.com/focus/index.jsp?topic=%2Fshell_7709%2FFOCUS_UsingFunctions%2Fsource%2Fpartition_aggr.htm

[David Beagan]

You can use reference lines to produce something like this:

(code is below)

TABLE FILE ggsalesSUM UNITS BY STON TABLE HOLD AS bystateENDTABLE FILE bystateSUM MIN.UNITS AS 'MIN' AVE.UNITS AS 'AVE' MAX.UNITS AS 'MAX'ON TABLE SET ASNAMES ONON TABLE HOLD AS LINEVALSEND-RUN-DEFAULTH &MIN = 0;-DEFAULTH &AVE = 0;-DEFAULTH &MAX = 0;-READFILE LINEVALS GRAPH FILE ggsalesSUM UNITSBY STON GRAPH PCHOLD FORMAT JSCHARTON GRAPH SET LOOKGRAPH BARON GRAPH SET AUTOFIT ONON GRAPH SET STYLE *INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $TYPE=DATA, COLUMN=N2, BUCKET=y-axis, $*GRAPH_JS"referenceLines": [ {"value": &MIN, "axis": "y", "line": {"color": "red", "width": 3, "dash": "dash"}, "label": {"text": "Minimum ", "color":"black", "font": "bold 10pt Sans-Serif"}, "anchor": "start", "showValue": true }, {"value": &AVE, "axis": "y", "line": {"color": "red", "width": 3, "dash": "dash"}, "label": {"text": "Average ", "color":"black", "font": "bold 10pt Sans-Serif"}, "anchor": "start", "showValue": true }, {"value": &MAX, "axis": "y", "line": {"color": "red", "width": 3, "dash": "dash"}, "label": {"text": "Maximum ", "color":"black", "font": "bold 10pt Sans-Serif"}, "anchor": "start", "showValue": true },]*ENDENDSTYLEEND

[Merin Joseph]

Awesome! let me try this. Thanks

[Merin Joseph]

Hi @David Beagan​ , this logic is working really well. but only thing is the avg and min values are overriding the graph. so i was trying to extend the x axis. DO you know any properties that can extend the x axis? thank you

[David Beagan]

That does seem like a reasonable thing to do. Unfortunately, looking at the manual, there doesn't appear to be a way to extend the reference line. A workaround woudl be to turn off the reference line labels and create an annotation with the max, min and ave.

[Merin Joseph]

Can we do hover over on reference line which shows the label and value ? so i can not show them on chart.

[David Beagan]

It is very useful that the text of the reference line handles HTML code. Based on the example I previously provided, I was able to do a tooltip:

 By using this code for a refernce line.

"referenceLines": [ {"value": &MIN, "axis": "y", "line": {"color": "red", "width": 3, "dash": "dash"}, "label": {"text": "<a title=' Min=&MIN' href='' style=color:orange;font-size:15pt;><br>&|#9632;</a>", "color":"black", "font": "bold 10pt Sans-Serif"}, "anchor": "start", "showValue": false },

[Merin Joseph]

@David Beagan​  awesome. this works. thank you soo much.