Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner
Partner

FirstSortedValue and Aggr in set analysis

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. 

1 Solution

Accepted Solutions
sunny_talwar

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

image.png

View solution in original post

7 Replies
sunny_talwar

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

Hmm, the file is too big to attach 

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

tomovangel
Partner
Partner
Author

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

 

 

tomovangel
Partner
Partner
Author

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

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

image.png

View solution in original post

tomovangel
Partner
Partner
Author

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
Partner
Partner
Author

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 🙂