topic Re: How to find the second absolute value with its sign in App Development

How to find the second absolute value with its sign

Metti — Sat, 16 Nov 2024 18:20:15 GMT

Hi,

I am trying to find the second maxium absolut value with respect to its dimension. So far I have managed to find its absolute value with below expression but I need to know what is its sign (positive (+) or negative (-)).

Does any one have an idea?

Max(Aggr(fAbs(Measure), firstDimension, secondDimension), 2)

Re: How to find the second absolute value with its sign

NitinK7 — Tue, 21 Jul 2020 14:14:23 GMT

you can try like

Num(Max(Aggr(Measure, firstDimension, secondDimension), 2), '#,##0;(#,##0)')

if number is negative(-) then it is show under bracket like (1235)

if number is positive (+) then it is show without bracket like 1234.

Re: How to find the second absolute value with its sign

Metti — Tue, 21 Jul 2020 14:22:35 GMT

Hi,

Your suggested expression will alwyas give me the second maximum value. I am trying to find the second Maximum absolute value. If I have a table like below, I would like to find -80. If I use your expression I will get 60.

Re: How to find the second absolute value with its sign

tresB — Tue, 21 Jul 2020 14:24:22 GMT

Try using FirstSortedValue(), like:

=FirstSortedValue(Num,-fabs(Num),2)

Re: How to find the second absolute value with its sign

Metti — Tue, 21 Jul 2020 14:37:12 GMT

I dont think your expression would work. You can try it in this dummy table where -80 should be the output.

Re: How to find the second absolute value with its sign

tresB — Tue, 21 Jul 2020 14:49:28 GMT

How exactly you are trying to get the output, in KPI object or in the same chart as a new column? Could you post your expected output in picture?

Re: How to find the second absolute value with its sign

Metti — Tue, 21 Jul 2020 16:15:25 GMT

I would like to show it as single value for kpi

In the screenshot that you attached, i don't see any dimension to aggregate on. I need to show the second maximum absolute value with respect to two dimension column.

Re: How to find the second absolute value with its sign

Kushal_Chawda — Tue, 21 Jul 2020 16:40:28 GMT

may be you have to use Aggr if you want it based on dimensions

=FirstSortedValue(aggr(sum(Measure),Dim1,Dim1),-fabs(aggr(sum(Measure),Dim1,Dim1)),2)

Where Measure,Dim1 & Dim2 are Your actual measure and dimensions respectively

Re: How to find the second absolute value with its sign

Metti — Wed, 22 Jul 2020 07:49:42 GMT

Perfect. This one worked 🙂