2 Replies Latest reply on Jun 4, 2013 2:16 PM by Kobe Royo

    Conditionally format reference lines & area charts

    Kobe Royo

      There are two different time periods of progress that are being tracked via bar/bullet chart ("Monthly Progress" sheet) and via line chart ("Year-over-Year" sheet).


      Used a Calculated Field, [Performance (color adj.)], to conditionally format [Performance], then dropped it to the Color Field to display accordingly.


      Following questions:

      1) On "Year-over-Year" sheet, the Reference Lines equal value of [Goal] and display accordingly. Since we are displaying 14 months of data, and goals change on a 12 month basis, Reference Lines will have 3 different values assuming [Goal] changes 1x/year.

      Is there a way to only display the first value of the reference line until the value changes? (i.e., currently reference line displays each value: Dec/11: 59%, Jan/12: 60%, Feb/12: 60%, Mar/12: 60%, ... Jan/13: 61%) Would be cleaner/less cluttered to only display the first value as the rest are repeats on a straight line.


      2) Also on "Year-over-Year" sheet, data is being displayed using Area Line charts. If we drop the same Calculated Field,[Performance (color adj.)], to the Color Field so the Area Line charts reflect [Performance] relative to [Goal], the chart derails. Any suggestions on how to conditionally format here?


      Aiming to put these two sheets next to each other, as can be seen on the dashboard in the workbook - any thoughts/comments are appreciated!


      Attached is the scrubbed .twbx



        • 1. Re: Conditionally format reference lines & area charts
          Tracy Rodgers

          Hi Kobe,


          For the first question, you may be able to create a calculated field similar to the following:




          Then, create a dual axis and have it act as the reference line. Then, you can choose which points are labeled.


          For the second question, change the calculation to be similar to the following:


          If total(sum([Performance])) >=total(sum( [Goal])) then "green"

          Elseif total(sum([Performance])) < total(sum([Goal])) then "red"



          Then, place this on the color shelf, right click and select Compute using...>Table (across)


          Hope this helps!



          • 2. Re: Conditionally format reference lines & area charts
            Kobe Royo

            Thanks, Tracy! About to try your suggestion for question 2 now; however, is it possible to create a dashed line when using the dual axis approach for question 1?

            And to confirm, there is no way to select which data points to display labels for when using the reference line approach?

            Thanks again