2 Replies Latest reply on Nov 21, 2012 9:03 AM by Wayne Hoy

    Coloring based on Aggregate Calc

    Wayne Hoy

      Hopefully this is simple... I've tried playing with the table calculation functions (window_sum, etc), but have run into aggregate/non-aggregate confusion.


      Please see the attached.  I'm trying to plot "employee compliance" by department, using horizontal bars that gradually "fill up" (turn darker).  The transactional data is very simple, one row per transaction.


      Easily accomplished, but I'd also like to change the color based on the overall rate of that department.  Say, if the department rate is 33% or less, use Red/Pink.  If the department rate is 34-66%, use Dark yellow/Light yellow.  If the rate is greater than 67%, use dark green/light green.


      Is this possible?




        • 1. Re: Coloring based on Aggregate Calc
          Catherine Rivier

          Hi Wayne,

          There are probably a lot of ways to accomplish that.  I'd personally skip the table calculations if your data stays in the format it is.


          Here's my solution.  I'm thinking of this as 6 different categories for color - Compliance High/Med/Low and the corresponding Noncompliance High/Med/Low.  I calculate each percentage using calculated fields, in 3 steps:

          1) ComplianceFlag (if Yes then 1 else 0)

          2) CompliancePercent (sum ComplianceFlag / sum Number of Records)

          3) ComplianceColorCodes (categorizing as High, Medium, and Low for your color-coding above)

          And I do the same with Noncompliance.


          Then, a dual axes chart, with CompliancePercent on one column axis, Avg(Number of Records) as the other.  (That Average Number of Records will always = 1; purpose is to put a solid 100% behind every record.  We can then fill consider this the remainder of the 100% bar which we can color however we choose.)  Make sure stack marks is on...


          All bars should now stack to 100%.  (If you don't see CompliancePercent in front, you have to right-click the Number of Records axis and select Move to Back.)


          So now we apply different color coding to each axis.  Under the Marks page main menu (upper right corner drop-down box), choose Multiple Mark Types.  Select the CompliancePercent field using the arrows below on the Marks page, and drag the measure ComplianceColorCodes into the Color selection.  Then select the Number of Records field, and drag the measure NoncomplianceColorCodes into that Color selection.


          Now it should be all formatting.  Change your legend colors, format the axes, etc.


          Hope this helps - as I said, I think there are a lot of solutions, this is one way I'd choose to do it.

          • 2. Re: Coloring based on Aggregate Calc
            Wayne Hoy

            Oh man, that's a cool trick with the 100% background bar.  Never would have thought of that.


            Does the job perfectly.  Thanks!