How To Do Snapshot Reporting with AsOf Fields
Snapshot Date between 'Job Action Effective Date' and 'Job Action End Effective Date
Example: 'Job Action Effective Date' <= DATE ('2020-05-01') AND 'Job Action End Effective Date' >= DATE('2020-05-01')
Example: IF 'Job Action Effective Date' <= DATE ('2020-05-01') AND 'Job Action End Effective Date' >= DATE('2020-05-01') THEN TRUE ELSE FALSE END
Fields to bring into your report to start:
- Employee ID
- Employee Record
- Job Action Effective Date
- Job Action End Effective Date
- Job Effective Sequence Number
How to create a set to filter on Job effective date for EAH views in Tableau:
- Create a calculated field in Tableau using the following logic:
- IF [Job Action Effective Date] <= DATE ('2020-05-01') AND [Job Action End Effective Date] >= DATE('2020-05-01') THEN TRUE ELSE FALSE END
- Call this field "Job Action Dates Flag"
- Right click on the field "Job Action Dates Flag", and 'Create' a 'Set'
- Name this set "Snapshot Dates between Job Action Effective Date and Job Action End Effective Date"
- Click on 'True'
- Click 'Ok'
- Save the report
- Now, you can instantly filter your report by dragging this set to the Filter card