Qlik Community

App Development

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jorn_vango
Contributor III
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
sunny_talwar

This seems to work

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

Capture.PNG

View solution in original post

6 Replies
sunny_talwar

May be this

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

jorn_vango
Contributor III
Contributor III
Author

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

sunny_talwar

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

jorn_vango
Contributor III
Contributor III
Author

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 ',');

sunny_talwar

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
Contributor III
Contributor III
Author

Thanks!!!