Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mitchjb2008
New Contributor III

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

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())

5 Replies

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())

mitchjb2008
New Contributor III

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

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

Thanks Sunny T!!

ramoncova06
Valued Contributor III

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

mitchjb2008
New Contributor III

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.

ramoncova06
Valued Contributor III

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

ok, then in that case aggr was the best option

Community Browser