6 Replies Latest reply: Feb 12, 2018 12:30 PM by Jorn Vangoidtsenhoven RSS

    FirstSortedValue function

    Jorn Vangoidtsenhoven

      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.