3 Replies Latest reply on Jun 23, 2013 4:35 PM by Kobe Royo

    Min&Max Shape Labeling per Pane / Conditionally Formatting Line Area Chart

    Kobe Royo

      Hi all, attempting to do two things here:

       

      1. Would like to place a shape only at the Max and Min points per pane, as depicted in the attached image ("MinMax.PNG") - thoughts?

       

      2. On "Year-over-Year Progress" sheet, a calculated field (conditionally formats color based on Performance relative to Goal) has been dragged to color shelf of the Line Chart segment of dual axis chart. However, when performance increases/decreases into another month's performance/color range (ex: from Red to Yellow) the line breaks as it conflicts between the two conditionally formatted regions.

      Is there any way to fix this? Any gradient options that can be applied? Any changes that can be adjusted in calculated field?

        • 1. Re: Min&Max Shape Labeling per Pane / Conditionally Formatting Line Area Chart
          Jim Wahl

          Hi Kobe,

           

          For (1), you can create a calculated field:

          performance min/max =

          (SUM([Performance]) = WINDOW_MAX(SUM(Performance)))

          OR

          (SUM([Performance]) = WINDOW_MIN(SUM(Performance)))

           

          Then add this field to the size shelf for the second set of marks, SUM(Performance) 2.

           

          Next change mark type to square. You see big and small squares. Click on the small "False" square in the size legend and select hide. Then click on size to make the True marks bigger.

           

          You're objective in (2) is at odds with (1), but first to get the result you want in (2), you need to use aggregates, which you probably want to be using regardless---your measures in the view are aggregates.


          To make Color - YoY Perfomance an aggregate, just wrap each Performance measure with SUM().

          Color - YoY Performance NEW =

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

          Elseif SUM([Performance]) <= SUM([Goal])*.5 then "red"

          Else "yellow"

          End

           

          But you can't have three mark types (area, line and square) in this graph. Option 1 gives you something close to your example png. You could enhance this with reference bands that colors the background red/green/yellow. Or you could add Performance min-max to the line size to emphasize these points.

           

          See attached.

           

          Jim

          • 2. Re: Min&Max Shape Labeling per Pane / Conditionally Formatting Line Area Chart
            Jim Wahl

            One other key point for (1). When you add performance min/max, you'll see multiple marks for min max.

             

            What's happening is that the table calc is partitioning on all the values dimensions in the view, which includes your Color - YoY Performance dimension. You're getting a min/max for each color.

             

            You could fix this with an advanced compute using, but I think it's better to just change the Color - YoY to an aggregate, as mentioned above. Now the field is no longer a dimension and the table calc won't be partitioned over it.

             

            Jim

            1 of 1 people found this helpful
            • 3. Re: Min&Max Shape Labeling per Pane / Conditionally Formatting Line Area Chart
              Kobe Royo

              Jim, your help is spot on - thanks so much!