FirstSortedValue and Aggr in set analysis

[tomovangel]

Hello guys, I am trying to calculate, the amount, where the last sorted status for each loan is defaulted_in_collection

My client is choosing 1 date from a field called ReportDate. 

The expression, should evaluate if avail_date is < Report Date, and if the last status for each loan is  defaulted_in_collection, and if both conditions are true, give me SUM(AMOUNT)

generatedDate is a Date field from the statuses table, status is a field  and statusid is the ID of the status change.

I have tried with the following expression, but it returns  0 values. 

Sum({$<avail_date={"<=$(=Only(ReportDate))"},aggr(firstsortedvalue({< generatedDate ={"<=$(=Only(ReportDate))"}>} status, -statusid),loan_app_id)='{defaulted_in_collection}'>}amount)

I have tried with the following IF statement, but it doesn't work as well. 

sum(
if(
Aggr(firstsortedvalue({< generatedDate ={"<=$(=Only(ReportDate))"}>} status, -statusid),ref_nr_fr1)='defaulted_in_collection'and avail_date<=ReportDate,amount))

Anyone has any ideas?

PS: The final result of the expression, should give me the sum(amount), where avail_date<ReportDate of all loan_app_id's that have status "defaulted_in_collection" assigned before the ReportDate. 

[sunny_talwar]

Would you be able to share some sample data with the output you expect to see from it?

[tomovangel]

Hmm, the file is too big to attach 

I will try to make the application smaller by cutting some of the data...

[tomovangel]

Attached is the file, and when I click the Dimension of the first table, I get the desired results in the second chart. 

[tomovangel]

This is the desired Output, which I get only after I click on the first table's dimension. 
The data is correct and checked for 01.01.2016.

Thanks sunny

[sunny_talwar · Accepted solution]

Try this expression for vTot

(Sum({<ref_nr_fr1 = {"=Match(FirstSortedValue(DISTINCT {<generatedDate={""<=$(=Only(ReportDate))""}>} status,-statusid), 'defaulted_in_internal_collection', 'defaulted_in_collection')"}>}
	if(avail_date <= ReportDate, amount))
-
Sum({<ref_nr_fr1 = {"=Match(FirstSortedValue(DISTINCT {<generatedDate={""<=$(=Only(ReportDate))""}>} status,-statusid), 'defaulted_in_internal_collection', 'defaulted_in_collection')"}>}
	if(payment_date <= ReportDate, sum)))

[tomovangel]

Thanks Sunny, It works as expected . 

So I can use the set analysis to define the conditions, before I write the If expression, thats good to know. 

And the way you used match function is good as well. Thank you so much 🙂 

[tomovangel]

Thanks Sunny, It works as expected . 

So I can use the set analysis to define the conditions, before I write the If expression, that's good to know. 

And the way you used match function is good as well. Thank you so much 🙂