Hi All,
This group has been very helpful in the past so hopefully you can help me solve this one!
I need to filter a straight table by a date in one of the columns but I need the results to also include the matching ID in another column. Currently the table displays transactions for the two most recent report dates. The rows are highlighted based on their status: green if it is a new item this week, yellow if the item changed between the 2 weeks, and red for a particular change. I want to only display the highlighted items and their respective matching rows from the previous week. My challenge is including the rows that have matching data in a separate column but don't fit the criteria of the filter.
Example:
An item with 'ID' of 1 is highlighted yellow for a change in amount (highlighting is based on 'Update Date' column). There are currently 2 rows for this item (both with 'ID' of 1), one with a 'Report Date' of 12/8/14 and one with a 'Report Date' of 12/15/14. When I filter on the 'Update Date' column as being >= 12/8/14 I get the row that changed with a 'Report Date' of 12/15/14 (which I want) but how do I also show the corresponding item with a 'Report Date' of 12/8/14. The earlier item does not have the same 'Update Date'.
Columns:
ID, Report Date, Update Date
1 12/8/14 12/9/14
1 12/15/14 10/3/14
As the Report Dates change every week to show the two most recent weeks, I have used variables
vPrevDate is a variable that contains the penultimate report date (12/8/14)
vMaxDate is a variable that contains the most recent report date (12/15/14)
Essentially, if Update Date>=vPrevDate, show that item AND the corresponding item where ID matches
So the first row above would display but I also need to display the second row above with the matching ID even though it doesn't fit the criteria.
I have thought about adding an additional column as a flag and then filtering on that column but I'm not sure how to write the expression.
Any suggestions are appreciated!!
