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

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

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!

• ###### Re: Using Max(Aggr(Count(..))) is not providing desired results

Can you try this:

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

• ###### Re: Using Max(Aggr(Count(..))) is not providing desired results

Wow, that was simple! Works exactly how I wanted it to!

Thanks Sunny T!!

• ###### Re: Using Max(Aggr(Count(..))) is not providing desired results

is your highest value always going to be 1.65 ?

if it is why not just divided it by that

=ColorMix1((Count(Distinct [Issues])*100/\$(AllIssues))/1.65),LightGreen(),LightRed())

if it might change then I would recommend adding it as a variable instead

• ###### Re: Using Max(Aggr(Count(..))) is not providing desired results

It won't always be 1.65...that's the problem. Based on selections and when I reload the app with new data every few days, that number will always change. That's the primary reason I wanted to have it come from an Aggr function.

• ###### Re: Using Max(Aggr(Count(..))) is not providing desired results

ok, then in that case aggr was the best option