Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate difference between consecutive rows filtered by SELECT DISTINCT

I need to calculate the difference between values in consecutive rows and display the value in the 2nd of each pair of rows, apparently without the help of an index.

Specifically, I created a calculated column, Workdays Since Filed, and now want to subtract the Row 1 Workdays value from the Row 2 value and display the result in Row 2 as a calculated measure.  

Values in red show how it should work:

PERMIT_IDACTION ACTION DATE FILED DATEWORKDAYS SINCE FILED (calculated)DAYS BETWEEN (calc)(Just to show math, not a real column)
FFXREC215FNSubmission Deficiencies Issued11/29/202111/12/202111  
FFXREC215FNNew Document Received12/6/202111/12/202116516 - 11
FFXREC215FNNew Document Received12/7/202111/12/202117117 - 16
FFXREC215FNWaiting for Information12/10/202111/12/202120320 - 17

 

Getting the "Days Between" calculation is complicated by the filters on this table that made using an index difficult. The main filter is PERMIT_ID and that could be indexed using dynamic filtering tricks I saw on other posts in this forum, but the filter that really hoses things up is a SELECT DISTINCT that removes duplicate rows from display when multiple new documents are received in the same day.

 

So the user sees this: 

 

Behind the scenes, the table actually looks like this:

PERMIT_IDACTIONACTION DATEFILED DATEWORKDAYS SINCE FILEDDAYS BETWEEN
FFXREC215FNSubmission Deficiencies Issued11/29/202111/12/202111 
FFXREC215FNNew Document Received12/6/202111/12/2021165
FFXREC215FNNew Document Received12/6/202111/12/2021160
FFXREC215FNNew Document Received12/6/202111/12/2021160
FFXREC215FNNew Document Received12/7/202111/12/2021171
FFXREC215FNNew Document Received12/7/202111/12/2021170
FFXREC215FNNew Document Received12/7/202111/12/2021170
FFXREC215FNWaiting for Information12/10/202111/12/2021203

 

I worked through solutions on about 5 similar posts but didn't find one that fit this issue specifically. Any ideas? 

 

Thanks,

Allison

 

 

  • Anonymous , Try a new column like

     

    New column =
    var _max = maxx(filter(Table, [PERMIT_ID] =earlier([PERMIT_ID]) && [ACTION DATE] <earlier([ACTION DATE])) , [ACTION DATE])
    return
    [WORKDAYS SINCE FILED] - maxx(filter(Table, [PERMIT_ID] =earlier([PERMIT_ID]) && [ACTION DATE] = _max) , [WORKDAYS SINCE FILED])

2 Replies

  • Anonymous , Try a new column like

     

    New column =
    var _max = maxx(filter(Table, [PERMIT_ID] =earlier([PERMIT_ID]) && [ACTION DATE] <earlier([ACTION DATE])) , [ACTION DATE])
    return
    [WORKDAYS SINCE FILED] - maxx(filter(Table, [PERMIT_ID] =earlier([PERMIT_ID]) && [ACTION DATE] = _max) , [WORKDAYS SINCE FILED])

    • Anonymous's avatar
      Anonymous
      Not applicable

      How do you get around the "EARLIER refers to an earlier row context which doesn't exist" problem? It's very stubborn.