topic How to change value based on Rank in App Development

How to change value based on Rank

Kaushik2020 — Thu, 27 Mar 2025 04:49:31 GMT

Dear All,

I have a below expression as a measure --> Value Column

Avg(Aggr(Sum({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Value),Code, Id)

The above expression generates value, -2, -1, 0, 1, 2 respectively.

Further below is my background expression to highlight top 10.

If(Rank(total Num(Avg(Aggr(Sum({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Value),Code, Id),'#,##0%')<=10,'#3D8D7A')

Requirement:

If the Value column is negative and in Top 10 (Green) and the value is -1 or -2, then change it to 2. Refer below sample.

thanks in advance.

Re: How to change value based on Rank

henrikalmen — Thu, 27 Mar 2025 09:39:12 GMT

Something like this: if(match([expression1], -1, -2) and [expression2]<=10, 2, [expression1])

So that would be:

if(match( Avg(Aggr(Sum({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Value),Code, Id), -1, -2) and Rank(total Num(Avg(Aggr(Sum({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Value),Code, Id),'#,##0%')<=10, 2, Avg(Aggr(Sum({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Value),Code, Id))

Re: How to change value based on Rank

henrikalmen — Mon, 31 Mar 2025 12:59:26 GMT

And you could probably shorten the expression by moving the set expression outside:

{$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>} if(match( Avg(Aggr(Sum(Value),Code, Id), -1, -2) and Rank(total Num(Avg(Aggr(Sum(Value),Code, Id),'#,##0%')<=10, 2, Avg(Aggr(Sum(Value),Code, Id))