Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Oh okay, makes sense... may be try this then
FirstSortedValue(DISTINCT {<payment_date={">=$(=Only(StartDate)) <=$(=Only(EndDate))"}>} LoanStatus,-DateChange)
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 Date
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)