Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Show latest status history based on highest value in text box

Dear All,

I have 2 tables, Transaction and Employee level history.

Transaction :

Emp_IDAmount
110
115
125
211
212

Employee_Level_History:

Emp_IDLevelEnd_Date_Level
1A01/01/2010
1B02/02/2010
2A03/03/2010
2C03/03/2010

Based on above data, i need to show latest employee level in text box, which that employee have the most amount.

So the result in text box should show level B.

Which is level B is owned by emp_id 1, that have the most amount.

How to solve that?

I can show the first emp_id in text box with below script, but i don't know how to show the latest level of highest emp_id amount.

=firstsortedvalue( Emp_ID, aggr(min(if(aggr(Rank(sum(Amount)),Emp_ID)=1,Emp_ID)),Emp_ID))

Many thanks,

Best Regards

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Take care that your End_Date_Level is interpreted as date:

Set DateFormat = 'MM/DD/YYYY'; // Or adjust to your date format

Then this should work in your text box:

=FirstSortedValue({<Emp_ID = {"=Rank(Sum(Amount))=1"}>} Level, -End_Date_Level)

View solution in original post

2 Replies
Highlighted
MVP
MVP

Take care that your End_Date_Level is interpreted as date:

Set DateFormat = 'MM/DD/YYYY'; // Or adjust to your date format

Then this should work in your text box:

=FirstSortedValue({<Emp_ID = {"=Rank(Sum(Amount))=1"}>} Level, -End_Date_Level)

View solution in original post

Highlighted
Partner
Partner

it works!

many many thanks