Your existing data set is very close, you just need the "day of collection for manual count" to be propagated over all the nights that are covered by the manual collection, not just the first night. This is best done as part of the shaping step in Tableau Prep. Without knowing the original source data I can guess at the following procedure:
- Left join your data set containing one record per night of automatic capture [AUTOMATIC] to your data set containing one record per day of manual collection [MANUAL], on the condition [AUTOMATIC].[NIGHT] <= [MANUAL].[DAY] AND [MANUAL].[DAY] - [MANUAL].[DAYS] + 1 <= [AUTOMATIC].[NIGHT] AND [MANUAL].[TRAP] = [AUTOMATIC].[TRAP].
- Ensure all the nightly records are included, and only the matching manual records are included.
- The day of manual collection [MANUAL].[DAY] should be available for each record covered by the same manual collection
- I have assumed the [AUTOMATIC].[NIGHT] contains the day of automatic capture, [MANUAL].[DAY] contains the day of manual capture, and [MANUAL].[DAYS] contains the previous days covered by manual capture, including the current day.
It worked! This is the first time I have posted on the forum and I really appreciate the awesome community here. Thanks for getting me un-stuck!