6 Replies Latest reply on Apr 9, 2010 7:12 AM by Bob Laverty

    Reference Line options - based on other calculated values

    Bob Laverty

      I have a stacked bar graph showing health care expenditures by category for a series of 18 months.  Three clients are profiled in the data.  I wanted to place reference lines in my data based on the average monthly costs per client in certain months (pre-intervention) and have the lines extend across the columns containing the remaining months (post-intervention).  The line was too low based on the "per pane" average because the "post" month values were included.  I couldn't use a moving average trend line because the dates are strings of YYYYMM. 

       

      I was able to set the lines based on the median value "per pane" since the "pre" values were more numerous and fairly consistent and a reasonable approximation of the visual effect I wanted.

       

      I created a set of the "pre" months, thinking that would help me create some aggregate value I could use, but I'm not sure how I would have incorporated that anyway.