Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm using set analysis expression below:
=Count({($<[Entry Date] = {"<=$(=EndDate)"}, [Entry Exit Has No Exit Date] = {'Yes'}>
+$<[Entry Date] = {"<=$(=EndDate)"}, [Exit Date] = {">=$(=StartDate)"}>
)*$<[Exit Destination] = {'Permanent housing (other than RRH) for formerly homeless persons (HUD)'}>}Distinct([Client Id]))
The formula returns 12, the data set only contains 9 values.
What I'm I doing incorrectly?
Hello,
I would suggest you to create flags in the backend for
entry <= enddate,
exit>=start date
Like
IF([Entry Date] <= EndDate,1,0) as Entry_End_Flag,
IF([Exit Date] >=StartDate,1,0) as Exit_Start_Flag,
Also assuming IF(len(Trim([Exit Date]))<1,'Yes', 'No') as [Entry Exit Has No Exit Date], /** this is the logic behind the field **/
In the chart try
=Count({
($<Entry_End_Flag={1}, [Entry Exit Has No Exit Date] = {'Yes'} +$<Entry_End_Flag={1}, Exit_Start_Flag={1}>)
*$<[Exit Destination] = {'Permanent housing (other than RRH) for formerly homeless persons (HUD)'}>}
Distinct ([Entry Exit Client Id]) )
Thank you for your suggestion. I'm don't have access to backend.
The issue is related to last record in the data set that is blank. I'm not certain why there is a blank row.
Entry Exit Client Id | Entry Exit Provider Name | Entry Date | Exit Date | Formula |
135781 | LSS - FM Faith on 8th | 5/23/2018 | 7/11/2018 | 1 |
83676 | LSS - FM Faith on 8th | 5/2/2018 | 7/26/2018 | 1 |
77068 | LSS - FM Faith on 8th | 5/1/2018 | 8/1/2018 | 1 |
5196 | LSS - FM Faith on 8th | 8/9/2018 | 8/10/2018 | 1 |
142643 | LSS - FM Faith on 8th | 9/1/2017 | 8/11/2018 | 1 |
71057 | LSS - FM Faith on 8th | 5/11/2018 | 8/17/2018 | 1 |
162330 | LSS - FM Faith on 8th | 8/9/2017 | 8/17/2018 | 1 |
66521 | LSS - FM Faith on 8th | 8/5/2018 | 10/22/2018 | 1 |
87056 | LSS - FM Faith on 8th | 6/3/2018 | 10/22/2018 | 1 |
- | - | - | - | 3 |