1 Reply Latest reply: Nov 14, 2011 6:11 AM by Erica Whalley RSS

    Format numbers on data point position

      Hi guys,

       

      I have some format question. As you see on the picture i have a combo chart. In this particular examle things just look great but most of the time when the numbers are different most of the percent numbers (bold) go into the bar numbers (italic) and nothing could be seen. I am adjusting the scales to somehow make it visible but it can't be done for all the times.I figured out that if the percent numbers could be moved below the data point things will be just fine. So my questions are could it be done? and How exactely to move the percent numbers position below the data point?

       

      Thank you!

        • Format numbers on data point position

          Hi Ice Horde.

           

          Unfortunately, there is no simple solution to this, but it is possible to work around. The way I would attack it is to set both the Y axis manually, so you know what they are. You could then use those values to work out the relative positions of the data points for your expressions and chuck it in an if statement in a symbol - only expression to show / not show / show both values.

           

          I would probably also set variables to hold the max / min for the charts to keep it a simple

           

          So, for example:

           

          1) Set variables that control both Y axis for your expressions, using the AGGR() function to get the max/min then multiply by an arbitrary value: eg v_Expr1_YMax =max(aggr(sum(Amount),[Activity Month])) *1.1

           

           

          2) Uncheck the "show values as data points" on your expression 2.

           

          3) Expand the expression 2 itself and  in the "show value" point add and if statement to only show it if they don't overlap: if(  [Expr1]/( v_Expr1_YMax -  v_Expr1_YMin) -  [Expr2]/( v_Expr2_YMax -  v_Expr2_YMin)  > 0.05,1,0) ie "If (distance of  *relative position Expr1* to *relative position Expr2* is greater than 5%,  then show it, otherwise don't"

           

          4) Add a third expression, which is just symbols that mimics the first expression, but minus an amount. This expression will sit below the first expression and only be exist if the two data points would overlap.

           

          So something like: if(  [Expr1]/( v_Expr1_YMax -  v_Expr1_YMin) -  [Expr2]/( v_Expr2_YMax -  v_Expr2_YMin)  > 0.05,[Expr1]  - ( v_Expr1_YMax -  v_Expr1_YMin)*0.01)

           

          ie, "If the points overlap, return the value of Expression 1 - a fraction of the y axis size"

           

          5) Check "show values as data points on this expression"

           

          Bit long winded - but let me know if it works.

           

          Regards,

           

          Erica