Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Could you describe more about your need?
I´m not following you
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
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
)