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


      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?