Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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!

1 Solution

Accepted Solutions
sunny_talwar

Can you try this:

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

View solution in original post

5 Replies
sunny_talwar

Can you try this:

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

Anonymous
Not applicable
Author

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

Thanks Sunny T!!

ramoncova06
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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.

ramoncova06
Specialist III
Specialist III

ok, then in that case aggr was the best option