3 Replies Latest reply: Jul 30, 2015 4:11 PM by Mark Diorio RSS

    Labels in gauge chart

      Hi,

       

      I have a gauge chart with the Min, Q1, Median, Q3 and Max segments.

       

      However the graphic scale doesn't match with this statistics. It just divide the bar in 4 equal segments.

       

      To ilustrate this problem please find below the attached example.

       

      How can I make the scale match to the defined segments?

       

      Many thanks

        • Labels in gauge chart
          John Witherspoon

          Well, I THOUGHT reference lines would work, but they don't.  You can add reference lines that match up to your segment boundaries, but when I checkmark "Show Label in Chart", it does nothing.  Bug?  Working as designed?  I can add the label as text in chart and stick it where it belongs, but then it won't be positioned at the right spot if your data changes.

          • Re: Labels in gauge chart
            Mark Diorio

            margin gauge labels.png

            I know this is an old post, but I just solved using a macro with logarithmic functions to determine x, y coordinates of labels.  You need fixed object ids for gauges and text objects for labels, and variables to for the quartile values in each gauge..

             

             

            sub SetGaugePointPositions

             

            GaugeObjectsList = "[TSGrossMargin],[TSEBITDAMargin],[TSOperatingMargin]"

            GaugeObjects = Split(GaugeObjectsList, ",")

            GaugeVarsList = "GrossMargin,EBITDAMargin,OperatingMargin"

            GaugeVars  = Split(GaugeVarsList, ",")

            PointsMetricsList = "LQ,Median,UQ"

            PointMetrics  = Split(PointsMetricsList, ",")

            for ii=LBound(GaugeObjects) to UBound(GaugeObjects)

             

              set  MyGauge = ActiveDocument.GetSheetObject(GaugeObjects(ii))

              set MyGaugePos = MyGauge.GetRect

              GaugeLeft=MyGaugePos.Left

              GaugeTop=MyGaugePos.Top

              GaugeWidth=MyGaugePos.Width

              GaugeHeight=MyGaugePos.Height

             

              ' figure center

              MyCenterX=GaugeLeft+(.44*GaugeWidth)

              MyCenterY=GaugeTop+(.44*GaugeHeight)

             

              'figure radius,circumference

             

              MyRadius=.45*GaugeWidth

              sCircumference=ActiveDocument.EvaluateEx("=2*pi()*"&MyRadius).Text

              sMin=ActiveDocument.EvaluateEx("=$(v"&GaugeVars(ii)&"Min)").Text

              sMax=ActiveDocument.EvaluateEx("=$(v"&GaugeVars(ii)&"Max)").Text

             

              for jj=LBound(PointMetrics) to UBound(PointMetrics)

               sMetricValue=ActiveDocument.EvaluateEx("=$(v"&GaugeVars(ii)&PointMetrics(jj)&")").Text

               'figure length of segment based on percent of circumference..

               sChordLength=ActiveDocument.EvaluateEx("((("&sMetricValue&"-"&sMin&")/(1.4*("&sMax&"-"&sMin&"))*"&sCircumference&")" ).text

             

               'figure angles in radians

               sAngle=ActiveDocument.EvaluateEx("((145+(("&sChordLength&"/"&sCircumference&")*360))*(pi()/180))").text

               'calculate x,y coordinates of lq, median, uq points

               sXPos=cint(ActiveDocument.EvaluateEx("num(("&cstr(MyRadius)&"*cos("&sAngle&"))+"&MyCenterX&",'###')").text)

               sYPos=cint(ActiveDocument.EvaluateEx("num(("&cstr(MyRadius)&"*sin("&sAngle&"))+"&MyCenterY&",'###')").text)

             

               'position lq, median, uq labels

               set MetricLabelObj = ActiveDocument.GetSheetObject("TG"&GaugeVars(ii)&PointMetrics(jj)&"Label")

               MetricLabelObjPos = MetricLabelObj.GetRect

               MetricLabelObjPos.Left=cint(sXPos)

               MetricLabelObjPos.Top=cint(sYPos)

               MetricLabelObj.SetRect MetricLabelObjPos

            next

            next

             

             

             

            end sub