get the last possible value with expression

[tomovangel]

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 ?

[sunny_talwar]

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?

[tomovangel]

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.

[sunny_talwar]

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)

[tomovangel]

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 Date

Thank you for giving thought into this Sunny

[sunny_talwar]

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

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

[tomovangel]

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

[sunny_talwar]

Try this

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

[tomovangel]

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

[sunny_talwar · Accepted solution]

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

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