Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlik4asif
Creator III
Creator III

Set analysis expression

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:

PnoCal ExprExplanation
10018701000-130 =870
100212701500-230 = 1270
100316902000-310 = 1690

 

i need Set expression for above Cal Expr

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Then try this:

=FirstSortedValue(Grec - Val, - ONo)

View solution in original post

9 Replies
tresesco
MVP
MVP

Try :

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

Kashyap_R
Partner - Specialist
Partner - Specialist

Hi

Try the below - Set Expression 

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

Thanks

Thanks and Regards
Kashyap.R
qlik4asif
Creator III
Creator III
Author

It wont be always max.

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

tresesco
MVP
MVP

Then try this:

=FirstSortedValue(Grec - Val, - ONo)

Kashyap_R
Partner - Specialist
Partner - Specialist

try this

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

Thanks and Regards
Kashyap.R
qlik4asif
Creator III
Creator III
Author

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

And i am using this expression in table

thi_pham
Creator III
Creator III

Table:

Dimension: PNo

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

 

qlik4asif
Creator III
Creator III
Author

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
Creator III
Creator III

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