Sort by greatest deviation

[Theo_Westseit]

Hello Guys,

I am currently stuck developing an application.
Ive created a table with three columns:

Is	Plan	Deviation
100	70	+30
5	10	-5
30	70	-40
70	50	+20

The Code at Column Deviation looks like this:

(Sum({<Year={"$(=max(Year))"}>}Is)
-
Sum({<Year={"$(=max(Year))"}, Version={"000"}>}Plan)

I would like to sort the Values in Column 3 by greatest Deviation (wherever its + or -)

So the Table should look like this:

Is	Plan	Deviation
30	70	-40
100	70	30
70	50	20
5	10	-5

Do you have any ideas? Thanks in advance!

[stevejoyce]

Have your Deviation Measure as the first sorted column and use Sort by expression, descending:

fabs(

(Sum({<Year={"$(=max(Year))"}>}Is)
-
Sum({<Year={"$(=max(Year))"}, Version={"000"}>}Plan)

)

[Theo_Westseit]

Thanks for your reply.

Unfortunately the operators (+/-) in front of the numbers disappear....but i want to keep them like this:

Is	Plan	Deviation
30	70	-40
100	70	30
70	50	20
5	10	-5

Any Ideas?

[Digvijay_Singh]

May be try Dual for the deviation column but it may have side effects.

Something like  - Dual(Deviation,abs(Deviation))

[Theo_Westseit]

Thanks, but it causes the same problem just like only using the Fabs() Function...the operators (+/-) in front of the numbers disappear...

Any further ideas? 😅

[Digvijay_Singh]

Thats strange because in the first parameter of dual we are not using Fabs, Dual displays value as per the first parameter but internally uses 2nd parameter in the calculations.

[Theo_Westseit]

I've just tried it again to make sure there is no mistake in my code:

Dual(

(Sum({<Year={"$(=max(Year))"}>}Is)
-
Sum({<Year={"$(=max(Year))"}, Version={"000"}>}Plan)

)

,Fabs(

(Sum({<Year={"$(=max(Year))"}>}Is)
-
Sum({<Year={"$(=max(Year))"}, Version={"000"}>}Plan)

)

))

But the operators disappear

[Digvijay_Singh]

See if the number formatting property if changed to 'measure expression' makes any difference

[stevejoyce · Accepted solution]

Yes I would go with Dual and number format as Auto.  you can then format the first parameter how you want dual(num(exp, '#,##0;-#,##0'), fabs(exp))

[Theo_Westseit]

@stevejoyceI have inserted the code with the fabs function and used Sort by expression -> descending...But the +/- operators disappeard

@Digvijay_SinghThis is the number formatting im currently using #.##0 $;-#.##0 $. Or do you mean something different?