Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This seems to work
FirstSortedValue({<CalculationGroup = {'EOM Balance'}>} Aggr(Sum({<CalculationGroup = {'EOM Balance'}>}Amount), TransactionDate), -Aggr(Only({<CalculationGroup = {'EOM Balance'}>}TransactionDate), TransactionDate))
May be this
FirstSortedValue({<CalculationGroup = {'EOM Balance'}>} Aggr(Sum({<CalculationGroup = {'EOM Balance'}>}Amount), TransactionDate), -TransactionDate)
Thanks Sunny, but it keeps displaying that pesky ' - ' in the table.
Would you be able to share a sample app to look at this?
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 ',');
This seems to work
FirstSortedValue({<CalculationGroup = {'EOM Balance'}>} Aggr(Sum({<CalculationGroup = {'EOM Balance'}>}Amount), TransactionDate), -Aggr(Only({<CalculationGroup = {'EOM Balance'}>}TransactionDate), TransactionDate))
Thanks!!!