Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

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

Re: get the last possible value with expression

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

Re: get the last possible value with expression

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?

Partner
Partner

Re: get the last possible value with expression

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.

Re: get the last possible value with expression

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)

Partner
Partner

Re: get the last possible value with expression

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

Sunny1.jpg

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

Sunny3.jpg

Thank you for giving thought into this Sunny

Re: get the last possible value with expression

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

Capture.PNG

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

Partner
Partner

Re: get the last possible value with expression

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

Re: get the last possible value with expression

Try this

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

Partner
Partner

Re: get the last possible value with expression

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

Sunny4.jpg

Re: get the last possible value with expression

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

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

View solution in original post