# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
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.

Labels (6)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

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

7 Replies
MVP

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

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

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

## Re: FirstSortedValue and Aggr in set analysis

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

MVP

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

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

## 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 🙂