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:

  1. Create a calculated field in Tableau using the following logic:
    1.  IF [Job Action Effective Date] <= DATE ('2020-05-01') AND [Job Action End Effective Date] >= DATE('2020-05-01') THEN TRUE ELSE FALSE END
  2. Call this field "Job Action Dates Flag"
  3. Right click on the field "Job Action Dates Flag", and 'Create' a 'Set'
  4. Name this set "Snapshot Dates between Job Action Effective Date and Job Action End Effective Date"
  5. Click on 'True'
  6. Click 'Ok'
  7. Save the report
  8. Now, you can instantly filter your report by dragging this set to the Filter card