topic Re: max value out from 3 straight table columns in QlikView

max value out from 3 straight table columns

Wed, 25 Nov 2020 16:16:04 GMT

I have straight table with 3 columns with diff scenario values... i need to find out the Max value out of 3 values. its a max of absolute numbers however i need to retain the sign .

Scenario-1	Scenario-1	Scenario-1	Max(Scenario)
120	-212	-830	-830
-388	990	113	990
-787	93	188	-787

Re: max value out from 3 straight table columns

Chanty4u — Thu, 01 Sep 2016 07:50:03 GMT

try

=FirstSortedValue(Senario,- Value)

Re: max value out from 3 straight table columns

Anil_Babu_Samineni — Thu, 01 Sep 2016 07:58:44 GMT

Perhaps This.

=FirstSortedValue(Max(Expression), Dim)

Re: max value out from 3 straight table columns

Or — Thu, 01 Sep 2016 08:17:41 GMT

There may be cleaner ways to do this, but this should work:

NumMax(fabs(Column(1),fabs(Column(2),fabs(Column(3)) *

If(NumMax(Column(1),Column(2),Column(3)) = NumMax(fabs(Column(1),fabs(Column(2),fabs(Column(3)),1,-1)

Explanation: First, we find the maximum absolute value.

Second, we check if this value is equal to the maximum non-absolute value. If it is, then it was a positive number (multiply by 1). If it isn't, then it was a negative number and we need to multiply by -1 to reverse the sign.

Re: max value out from 3 straight table columns

effinty2112 — Thu, 01 Sep 2016 08:32:25 GMT

Dear Udaya,

This perhaps?:

Data:

LOAD * INLINE [

Scenario, Value

1, 120

1, -212

1, -830

2, -388

2, 990

2, 113

3, -787

3, 93

3, 188

];

Then

Scenario	FirstSortedValue(Value,-Fabs(Value))
	990
1	-830
2	990
3	-787

Cheers

Andrew

Re: max value out from 3 straight table columns

HirisH_V7 — Thu, 01 Sep 2016 08:34:08 GMT

Hi,

May be like this,

Using this expression,

=Sum(IF(fabs([Scenario-3])>fabs([Scenario-1]) and fabs([Scenario-2]),[Scenario-3],
IF(fabs([Scenario-1])>fabs([Scenario-2]) and fabs([Scenario-3]),[Scenario-1],
IF(fabs([Scenario-2])>fabs([Scenario-1]) and fabs([Scenario-3]),[Scenario-2]))))

Hope this helps,

PFA,

Hirish

Re: max value out from 3 straight table columns

Thu, 01 Sep 2016 13:07:04 GMT

Thank you everyone with valuable inputs. I had similar idea what Shoham had suggested, until we have clear solution i am going to make use of this expression. its working...thank you everyone!