topic Re: Maximum value in Class function results in QlikView

Maximum value in Class function results

Fri, 02 May 2014 19:04:17 GMT

I am trying to apply heat map formatting to my chart which would be driven by the max value within the chart; In the example below, I'm after the 372. I've tried embedding the class within an aggr to carve out the max, but that clearly doesn't work. I'm curious if anyone has found a solution. Any insight would be great! Thanks!

Re: Maximum value in Class function results

Clever_Anjos — Fri, 02 May 2014 19:55:00 GMT

Could you describe more about your need?

I´m not following you

Re: Maximum value in Class function results

rwunderlich — Fri, 02 May 2014 21:42:46 GMT

As you've noted, you typically would use aggr() to find the max but aggr() won't operate with a calculated dimension as far as I know. Here's an alternative I think will work. Assume your value expression is "count(Num)".

=RangeMax(Top(

RangeMax(before(count(Num),0,999))

,1,999))

-Rob

http://masterssummit.com

Re: Maximum value in Class function results

Mon, 05 May 2014 11:37:22 GMT

Thanks for the insight, Rob. I think I'm close. Since the dimension is sorted alphabetically the result is looking at the max value per column, and formatting accordingly. I can get the formatting correct by sorting by the count(Num) expression, however I would prefer alphabetically. I included the pic of what this is returning. I'm wondering if I can dynamically resort after formatting, or somehow use your formula to grab the max value of the entire result, and format based on that result. I can live with the change in sorting, but thought I'd see if you have any other thoughts. Thanks!

Here is the background formatting I am using on the expression:

Forced Max:

rgb(
       200 - (10/360)*count(Num), // red
       200 - (200/360)*count(Num), // green
       200 - (200/360)*count(Num) // blue
)

Calculated Max:

rgb(
       200 - (10/RangeMax(Top(RangeMax(before(count(Num),0,999)),1,999)))*count(Num), // red
       200 - (200/RangeMax(Top(RangeMax(before(count(Num),0,999)),1,999)))*count(Num), // green
       200 - (200/RangeMax(Top(RangeMax(before(count(Num),0,999)),1,999)))*count(Num) // blue
)