Incorrect count

[jbakerstull]

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?

[vamsee]

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]) )

[jbakerstull]

Thank you for your suggestion. I'm don't have access to backend.

[jbakerstull]

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