Set analysis expression

[qlik4asif]

Guys i need help in set expression

Test:
Load * Inline
[
PNo,ONo,Val,Grec
1001,10,120,1000
1001,20,130,1000
1002,10,210,1500
1002,20,220,1500
1002,30,230,1500
1003,10,310,2000

];

Desired Output:

Pno	Cal Expr	Explanation
1001	870	1000-130 =870
1002	1270	1500-230 = 1270
1003	1690	2000-310 = 1690

i need Set expression for above Cal Expr

[qlik4asif]

Guys i need help in set expression

Test:
Load * Inline
[
PNo,ONo,Val,Grec
1001,10,120,1000
1001,20,130,1000
1002,10,210,1500
1002,20,220,1500
1002,30,230,1500
1003,10,310,2000

];

Desired Output:

Pno	Cal Expr	Explanation
1001	870	1000-130 =870
1002	1270	1500-230 = 1270
1003	1690	2000-310 = 1690

i need Set expression for above Cal Expr

[tresesco]

Try :

=Max(Grec)-FirstSortedValue(Val, -ONo)

[Kashyap_R]

Hi

Try the below - Set Expression 

                                      =Max(Aggr((Max(Grec)-Max(Val)),PNo))

Thanks

[qlik4asif]

It wont be always max.

We need to consider max of ONo across PNo and their respective val and Grec

[tresesco · Accepted solution]

Then try this:

=FirstSortedValue(Grec - Val, - ONo)

[Kashyap_R]

try this

Min(Aggr((Max(Grec)-Max(Val)),ONo,PNo))

[qlik4asif]

It is working for sample data but not the actual data.

And i am using this expression in table

[thi_pham]

Table:

Dimension: PNo

Measure: Cal Expr = max(Grec) - Max(val).

[qlik4asif]

It is not always the case like we need to consider the max value.

see below sample data, which i modified

Test:
Load * Inline
[
PNo,ONo,Val,Grec
1001,10,120,1000
1001,20,130,1000
1002,10,210,1500
1002,20,250,1500
1002,30,220,1500
1003,10,310,2000

1003,20,110,2000

];

Desired Output in table:

PNo Cal Exp

1001 870  -------1000-230

1002 1280  -----1500-220

1003 1890  -----2000-110

Note:Need to consider the values related to Max. ONo, but not the max of Measures.

[thi_pham]

Please try: max([Grec]) - FirstSortedValue(Val, -ONo)