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 ?

[tomovangel]

Okay, this gives me the selected +1, but Im thinking, what if the expression is right, and the returned amount will be the day+1, so Im not sure if I should implement it on the production environment..

[swuehl]

But

So I guess, One thing might be, that there is 2 payments made on 30.03.2018, and my formula uses Only,

indicates a timestamp, only formatted as date.

[swuehl]

Why don’t my dates work?

[tomovangel]

Okay, You are right, when I second check the SQL Data, it is with a timestamp.

So, my next question is how can we format the expression, to work through the timestamp?

[sunny_talwar]

I agree with Stefan, because you are seeing two rows means that they are timestamp. I would fix this in the script using Floor function

Date(Floor(payment_date), 'DD.MM.YYYY') as payment_date

[swuehl]

Maybe use Sunny's expression with a smaller comparison rather than smaller equal

Sum({<paymentdate={">=$(=Only(StartDate)) <$(=Date(Only(EndDate)+1, 'DD.MM.YYYY'))"}>}total_loan)

or create a pure date for paymentdate using Dayname(paymentdate) as paymentdate  in the script

[sunny_talwar]

Just out of curiosity, the same set analysis in other table was able to show 30.03.2018?

[tomovangel]

swuehl‌

Date(Floor(payment_date), 'DD.MM.YYYY') as payment_date

This expression is enough in the loadscript, so Now all expression are working as expected !!!

Thank you Sunny, for bearing this last hour with me,
And Thank you Stefan, for pointing out this

[swuehl]

Sunny Talwar &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; wrote:

Just out of curiosity, the same set analysis in other table was able to show 30.03.2018?

I assume the same expression was not used in some 50 charts, but a similar set expression operating on other date fields (which are probably not timestamp fields).