Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 

get the last possible value with expression

Hello guys, I have been struggling the last few days with the following situation.

We have 2 tables, 1 is with repayments for Loans, and the second table is with Statuses of each loan.

first table is like this

Loan ID, Loan Type, Payment Date, Payment

the second table is like this

Loan Id, Loan status, Date change

So i have build a set analysis expression to get the sum between 2 dates .

In my straight table i have

Loan Id, Payment date and sum of payments. Everything is working great

But when i add FirstSortedValue(LoanStatus,-DateChange) the expression returns results for ALL Loans, and thus i get quite a few empty fields, but it should return the value only for the loans that have payments in this period....

How can I get the last status ?

1 Solution

Accepted Solutions

Oh okay, makes sense... may be try this then

FirstSortedValue(DISTINCT {<payment_date={">=$(=Only(StartDate)) <=$(=Only(EndDate))"}>} LoanStatus,-DateChange)

View solution in original post

28 Replies

Can there be multiple LoanStatuses on a single date? Also, are you trying this on a chart where you have Loan ID as your dimension?


Yes, there can be multiple statuses, but the date is with timestamp, so it's not a problem.

And yes, I have the loan ID as dimension in the chart.

I don't see why this should be a problem if datechange is a timestamp. For just checking can you add Max(DateChange) as one of the expression and see what values does it come up with. Also, just for testing, can you add this expression FirstSortedValue(DISTINCT LoanStatus,-DateChange)


Just to clarify something, In the report I have 2 fields which are not connected to the database, but are just used for selection, they are StartDate and EndDate.

The expression I am using for Paid Amount is

Sum({<payment_date={">=$(=Only(StartDate)) <=$(=Only(EndDate))"}>}payment)

This is a screenshot from the report without datechange sorted by Payment Date


And this is screenshots with max(datechange) and firstsortedvalue(distinct loanstatus,-datechange) again, ordered by Payment DateSunny2.jpg


Thank you for giving thought into this Sunny

So, I do see your firstsortedvalue() function returning something...


Is this not right? What are you hoping to see instead?


You can see in the first picture, the returned values are only in the time period specified (01.05.2018 - 31.05.2018),

And then, when I add Firstsortedvalue() function, it returns information for all loan id's and you can see that even the payment date is not correct...

Try this

FirstSortedValue(DISTINCT {<payment = {"<>0"}>} LoanStatus,-DateChange)


It returns the same value... as Firstsortedvalue(distinct LoanStatus,-DateChange)


Oh okay, makes sense... may be try this then

FirstSortedValue(DISTINCT {<payment_date={">=$(=Only(StartDate)) <=$(=Only(EndDate))"}>} LoanStatus,-DateChange)

View solution in original post