Trigger table update on filter change

[AndrewS]

Hi everyone,

TLDR: I have a problem where updating a filter does not update relevant table fields that compare that filter to a static date. Is there a way to trigger a table refresh on filter update?

Background:

I have a set of employee personnel records. Each employee has multiple rows, and each row has a start and end date. I'd like to be able to select a date from a filter bar, and have only records where the selected date is after the start date and before the end date.

To that end I have a calendar table with every date from 1990 to 2023 called [Report Date]. This calendar is not joined to my data table in any way. I have created a variable called v_ReportDate with the following logic.

=if(isnull(GetFieldSelections([Report Date])), today(),GetFieldSelections([Report Date]))

Next my sheet is just a simple table with relevant employee information ending in a set of columns with the following logic:

=if(date([start date], 'MM/DD/YYYY') <= date(V_ReportDate,'MM/DD/YYYY'),'YES','NO')

=if(date([end date], 'MM/DD/YYYY') > date(V_ReportDate,'MM/DD/YYYY'),'YES','NO')

I have also added a column =v_ReportDate just for testing.

My eventual plan is to combine the two with an AND then remove the 'NO' and uncheck "Include null values".

Problem:

As to my problem, the logic works on App load with the Today() date. When I update the report date filter, I see the report date column update, but the other comparison fields remain static. If I move to another sheet and back to the original, the comparison fields do update. Edit: Not the case. I have to change the field expression to have the comparison update.

To me this indicates that even though the variable is updating, the comparison logic needs a refresh command. Is it possible to trigger this refresh on filter selection?

[Dalton_Ruer]

I'd suggest you handle your use case using the IntervalMatch function inside your load script so that you can create a simple association. Of course I always like to handle CPU tasks that won't change in the load script so it's only used 1 time and then user experience is faster. 

If you have never used IntervalMatch here is some example code.

Data:
Load * Inline [
SalesID, Date, SalesAmt
1, 6/27/2020, 1203039
2, 6/28/2020, 1026330
3, 7/27/2020, 1149891
4, 8/27/2020, 9193930
];

IntervalData:
Load * Inline [
IntervalID, StartDate, EndDate
1, 6/1/2020, 6/30/2020
2, 7/1/2020, 7/31/2020
3, 8/1/2020, 11/14/2022
];

Intervals:
IntervalMatch (Date) Load StartDate, EndDate
resident IntervalData;

Join (Intervals)
Load
StartDate,
EndDate,
IntervalID
Resident IntervalData;

drop table IntervalData;

[AndrewS]

Thanks for responding Dalton.

Unfortunately, my data doesn't have neat intervals. Each employee has multiple entries, and a period ends on the next update to the record. While some of these dates will line up nicely, many are one off dates (think date of hire, promotion, transfer, termination, etc). Many intervals would overlap, and finding all unique intervals brings me back to my original problem of only displaying intervals that contain a specific date.