FirstSortedValue function

[Anonymous]

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.

[sunny_talwar]

May be this

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

[Anonymous]

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?

[Anonymous]

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 · Accepted solution]

This seems to work

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

[Anonymous]

Thanks!!!