Hi,
I have a table of document changes. Each row contains a document id and a time when the change occurred. I need to report on this data in two modes: one that reports on all changes, and another that reports on only the most recent changes for a given document. In order to do this, I think I need a calculated field that shows whether the given row represents the most recent change. As an example, here's what I have:
<tbody>
</tbody>
I think what I want is a calculated column that has a 1 if the row is the most recent change, and a 0 if not - then that column can be used as a filter and also to sum and in various other calculations.
My problem is that I can't figure out how to calculate this. My first thought was something like:
But lastdate() wants just a column, not the entire table that filter() spits out. Same with max(). I can't figure out how to narrow down the return of filter() to a single column for the benefit of lastdate(), and I can't figure out an alternative to lastdate() (or max(), which also expects a column) to use instead. Am I missing something? Should I take a different approach?
Many thanks!
I have a table of document changes. Each row contains a document id and a time when the change occurred. I need to report on this data in two modes: one that reports on all changes, and another that reports on only the most recent changes for a given document. In order to do this, I think I need a calculated field that shows whether the given row represents the most recent change. As an example, here's what I have:
change_date | document_id |
5/1/2015 | a |
5/1/2015 | b |
5/1/2015 | c |
5/1/2015 | d |
6/20/2015 | a |
6/20/2015 | b |
6/20/2015 | c |
6/20/2015 | e |
6/20/2015 | f |
7/11/2015 | a |
7/11/2015 | b |
7/11/2015 | f |
7/11/2015 | g |
<tbody>
</tbody>
I think what I want is a calculated column that has a 1 if the row is the most recent change, and a 0 if not - then that column can be used as a filter and also to sum and in various other calculations.
My problem is that I can't figure out how to calculate this. My first thought was something like:
Code:
=if(
lastdate( filter( Table, Table[document_id] = [document_id] ) ) = [change_date],
1, 0
)
But lastdate() wants just a column, not the entire table that filter() spits out. Same with max(). I can't figure out how to narrow down the return of filter() to a single column for the benefit of lastdate(), and I can't figure out an alternative to lastdate() (or max(), which also expects a column) to use instead. Am I missing something? Should I take a different approach?
Many thanks!