Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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)
)
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?
May be try Dual for the deviation column but it may have side effects.
Something like - Dual(Deviation,abs(Deviation))
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? 😅
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.
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
See if the number formatting property if changed to 'measure expression' makes any difference
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))
@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?