Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jorn_vango
New Contributor III

FirstSortedValue function

The data set is the following:

TransactionDate, TD_QuarterYear, CalculationGroup, LoanID, Amount

9/20/2017, 2017-Q3, 'FPD Point', 1122, $450

9/30/2017, 2017-Q3, 'EOM Balance',3324, $510

10/30/2017, 2017-Q4, 'EOM Balance', 1243, $200

10/30/2017, 2017-Q4, 'FPD Point', 1110, $210

10/30/2017, 2017-Q4, 'EOM Balance', 2110, $430

11/2/2017, 2017-Q4, 'FPD Point', 2332, $102

11/3/2017, 2017-Q4, 'FPD Point', 1220, $140

12/20/2017, 2017-Q4, 'FPD Point', 1133, $210

12/30/2017, 2017-Q4, 'FPD Point', 2300, $100

12/31/2017, 2017-Q4, 'EOM Balance', 2220, $220

12/31/2017, 2017-Q4, 'EOM Balance', 3001, $200

For each quarter, I need to get the Sum of the amounts where the CalculationGroup = 'EOM Balance' and where the TransactionDate is the last day of the Quarter.

My table has the quarter as a dimension but SUM(IF(CalculationGroup='EOM Balance', amount) will sum up all 'EOM Balance' for the entire quarter.  'EOM Balance' means the End Of Month balance per loan, so when looking at the 'end of month balance' for a quarter, I only need to be looking at the end of month for the last month in the quarter.

I looked at FirstSortedValue and tried:

FirstSortedValue( Sum( If( CalculationGroup = 'EOM Balance'), Amount), Max(TransactionDate) )

However, this shows ' - ' and does not provide what is needed.  Any help is greatly appreciated, thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: FirstSortedValue function

This seems to work

FirstSortedValue({<CalculationGroup  = {'EOM Balance'}>} Aggr(Sum({<CalculationGroup  = {'EOM Balance'}>}Amount), TransactionDate), -Aggr(Only({<CalculationGroup  = {'EOM Balance'}>}TransactionDate), TransactionDate))

Capture.PNG

6 Replies
MVP
MVP

Re: FirstSortedValue function

May be this

FirstSortedValue({<CalculationGroup  = {'EOM Balance'}>} Aggr(Sum({<CalculationGroup  = {'EOM Balance'}>}Amount), TransactionDate), -TransactionDate)

jorn_vango
New Contributor III

Re: FirstSortedValue function

Thanks Sunny,  but it keeps displaying that pesky ' - ' in the table.

MVP
MVP

Re: FirstSortedValue function

Would you be able to share a sample app to look at this?

jorn_vango
New Contributor III

Re: FirstSortedValue function

I have a sample workbook but how do I upload it?

The formula works if there is only 1 value for the last day of the quarter, like 9/30/2017 in the sample data set provided.  The formula shows $510 for Q3.

It doesn’t work for quarters where there are multiple values on the last day, like 12/31/2017.  It should show $220 + $200 = $420, but it displays ' - '.

Every quarter will have multiple values in the real world since each loan will have an ‘eom’ value.  All of these values have to be summed up for the last day, e.g. 12/31/2017.  Thanks.

Temp:

LOAD * Inline [

TransactionDate, TD_QuarterYear, CalculationGroup, LoanID, Amount

9/20/2017, 2017-Q3, 'FPD Point', 1122, $450

9/30/2017, 2017-Q3, 'EOM Balance',3324, $510

10/30/2017, 2017-Q4, 'EOM Balance', 1243, $200

10/30/2017, 2017-Q4, 'FPD Point', 1110, $210

10/30/2017, 2017-Q4, 'EOM Balance', 2110, $430

11/2/2017, 2017-Q4, 'FPD Point', 2332, $102

11/3/2017, 2017-Q4, 'FPD Point', 1220, $140

12/20/2017, 2017-Q4, 'FPD Point', 1133, $210

12/30/2017, 2017-Q4, 'FPD Point', 2300, $100

12/31/2017, 2017-Q4, 'EOM Balance', 2220, $220

12/31/2017, 2017-Q4, 'EOM Balance', 3001, $200

] (delimiter is ',');

MVP
MVP

Re: FirstSortedValue function

This seems to work

FirstSortedValue({<CalculationGroup  = {'EOM Balance'}>} Aggr(Sum({<CalculationGroup  = {'EOM Balance'}>}Amount), TransactionDate), -Aggr(Only({<CalculationGroup  = {'EOM Balance'}>}TransactionDate), TransactionDate))

Capture.PNG

jorn_vango
New Contributor III

Re: FirstSortedValue function

Thanks!!!