5 Replies Latest reply: May 11, 2016 1:51 PM by Ramon Covarrubias RSS

    Using Max(Aggr(Count(..))) is not providing desired results

    Mitchell Boyer

      Hi everyone! I'm trying to do a ColorMix1 for a table, but I'm stumped.

       

      I have a table in my app that looks something like this:

       

      EngineIssues Rate
      Engine 3

      1.65

      Engine 11.01
      Engine 20.73
      ......

       

      Where '% of Issues' is Count(Distinct [Issues])*100/$(AllIssues). So a pretty basic ratio (normalized by every 100 engines produced).

       

      To use ColorMix1, I've figured out that the value has to be between 0 and 1...I can't figure out how to normalize these values such that Engine 3 becomes 1 for the ColorMix1 function.

       

      Essentially, the math to get Engine 3 Issue Rate from 1.65 to 1.0 and scale the rest of the values accordingly is the issue.

       

      To get around this, I was trying to make the Aggr function work such that:

       

      =ColorMix1(Count(Distinct [Issues])*100/$(AllIssues) / Max({$<Engine=>}Aggr(Count({$<Engine=>}Distinct [Issues])*100/$(AllIssues),Engine)),LightGreen(),LightRed())

       

      where I could get the Max Engine value of the expression. This doesn't return what I'm expecting; I'd like the maximum value of the Aggr function's expression. The Max of the Aggr function is only reporting the Max relative each engine.

       

      How do I make the denominator a static value, especially if I can't assigned a variable using the Aggr function in the load script?

       

      Thanks!