Dear Tableau Gurus,
I am attempting to complete trend analysis on my data using a date parameter. In my database people are either Active or Inactive. It is a database related to homelessness, if an individual is Active, it means they are experiencing homelessness at that time. They will have a status of Housing Plan or No Housing Plan. Basically, I would like to see how many people are Active on any given day. The data is generated from Start and End Dates of an Episode. The problem I am running into, is if a person has experienced more than one episode of homelessness, all of their episode start and end dates are being captured, thus inflating the Active numbers on that day.
As an example, I set my parameters to 11/26/2017, if John Doe experienced homelessness on 3/4/2015 but was then housed on 5/3/2015, he will have an episode start in 2015 and an end date in 2015. If he then experiences recidivism into homelessness on 2/1/2019 until 12/9/2019, he should only show up in 2015 and 2019. However, he shows up on my parameter date of 11/26/2017 because he does have a Start Date < 11/26/2017 and an End Date > 11/26/2017. But he was not actually Active on that date. Every person experiencing homelessness in my database has different Episode Dates and one person may have as few as one episode or as many as seven episodes of homelessness.
Please note, I cannot share my workbook as there is personally identifiable information on it.
This is what I've come up with:
IF( ([Episode Start Date] <= [Start Dates]
AND [Episode End Date] >= [End Dates])
AND ([ReviewStatusValue] = 'Housing Plan'
OR [ReviewStatusValue] = 'No Housing Plan'
) )THEN 'Active'
ELSEIF (([Episode Start Date] <= [Start Dates] AND ISNULL([Episode End Date]))
AND ( [ReviewStatusValue] = 'Housing Plan'
OR [ReviewStatusValue] = 'No Housing Plan'))
THEN 'Active'
END
Any assistance would be greatly appreciated!
Thanks,
Lindsey
Hi Lindsey. Kevin mentioned you reached out to him. Seems this one totally slipped off my radar. My apologies!
I think I understand the problem here. Take the following person, for example:
If you set your date frame for 3/1/2015 to 4/1/2020, you wish to count this person only once.
If you set your date frame from 4/1/2017 to 6/1/2017, then this person should be counted once.
If you set your date frame from 1/1/2014 to 12/31/2014, then this person should not be counted.
But what if your date frame is 1/2/2015 to 3/1/2015? The end date comes within this person's first episode but the start date is outside of it? Should this count? If so, are we basically counting any person who's start/end period overlaps with your start/end period? If so, try the following calculated field.
Matches
// Count as a match if there is any overlap.
IF [Start Dates]>=[Episode Start Date] AND [Start Dates]<=[Episode End Date] THEN
// Start date parameter falls within the episode start and end dates.
[Veteran ID]
ELSEIF [End Dates]>=[Episode Start Date] AND [End Dates]<=[Episode End Date] THEN
// End date parameter falls within the episode start and end dates.
[Veteran ID]
ELSEIF [Start Dates]<=[Episode Start Date] AND [End Dates]>=[Episode End Date] THEN
// Episode period falls completely within the parameters.
[Veteran ID]
ELSE
// No overlap
NULL
END
Then you can just do a count distinct on this field. I think that should work.
