Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
qlik4asif
Contributor II

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
MVP
MVP

Re: Set analysis expression

Then try this:

=FirstSortedValue(Grec - Val, - ONo)

View solution in original post

9 Replies
MVP
MVP

Re: Set analysis expression

Try :

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

Partner
Partner

Re: Set analysis expression

Hi

Try the below - Set Expression 

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

Thanks

qlik4asif
Contributor II

Re: Set analysis expression

It wont be always max.

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

MVP
MVP

Re: Set analysis expression

Then try this:

=FirstSortedValue(Grec - Val, - ONo)

View solution in original post

Partner
Partner

Re: Set analysis expression

try this

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

qlik4asif
Contributor II

Re: Set analysis expression

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

And i am using this expression in table

thi_pham
Contributor

Re: Set analysis expression

Table:

Dimension: PNo

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

 

qlik4asif
Contributor II

Re: Set analysis expression

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
Contributor

Re: Set analysis expression

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