Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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

Re: FirstSortedValue and Aggr in set analysis

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

Re: FirstSortedValue and Aggr in set analysis

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

Re: FirstSortedValue and Aggr in set analysis

Hmm, the file is too big to attach 

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

Partner
Partner

Re: FirstSortedValue and Aggr in set analysis

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

 

 

Partner
Partner

Re: FirstSortedValue and Aggr in set analysis

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

Re: FirstSortedValue and Aggr in set analysis

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

Partner
Partner

Re: FirstSortedValue and Aggr in set analysis

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 🙂 

Partner
Partner

Re: FirstSortedValue and Aggr in set analysis

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 🙂