Weekly Census - Hard Challege

[jbakerstull]

My majority of my data is made up of clients that have date ranges (data set below).  I'm trying to create a weekly census that would count number of clients that reside in a project. The report has two date filters, the start date will always be a Monday and end date Sunday value. 

I want to create a report weekly report that counts each day a client is served with Monday being the start of week.  If the week selected was 11/12/18 (Monday) through 11/18/18 (Sunday). In the data table, I would like to have a column for each day of the week either with a 1 or 0 value. The end goal would be to aggregate total values in a  KPI for each day of the week. 

Example client #166788, Monday would be a value of 1, Tuesday through Sunday values would be 0.

Client # 21393, Monday value would be 0, but Tuesday through Sunday the value for the client would be one.  

Entry Exit Client Id	Entry Date	Exit Date
166778	11/10/2018	11/12/2018
77068	11/9/2018	11/12/2018
53962	11/2/2018	11/12/2018
87646	7/18/2018	11/12/2018
171743	11/9/2018	11/13/2018
131280	11/17/2018	-
115099	11/15/2018	-
133326	11/14/2018	-
21393	11/13/2018	-
167898	11/13/2018	-

I know that I'm asking for a lot. I only have access to user end. Any suggestions or thoughts on how to start this project would be greatly appreciated. 

[JustinDallas]

You could try something like this.

ClientData:
LOAD *,
[Entry Exit Client Id] AS '%entry_exit_client_date_key',
Date#([Entry Date Text],'MM/DD/YYYY') AS 'Entry Date',
Date#([Exit Date Text],'MM/DD/YYYY') AS 'Exit Date'
;
LOAD * Inline
[
'Entry Exit Client Id',	'Entry Date Text',	'Exit Date Text'
166778,	11/10/2018,	11/12/2018
77068,	11/9/2018,	11/12/2018
53962,	11/2/2018,	11/12/2018
87646,	7/18/2018,	11/12/2018
171743,	11/9/2018,	11/13/2018
]
;

DROP FIELDS [Entry Date Text], [Exit Date Text]
;

EntryExitDates:
LOAD
    "Entry Exit Client Id" AS '%entry_exit_client_date_key',
  	Date(StartDate + IterNo() - 1) As 'Entry Exit Date'
While StartDate + IterNo() - 1 <= EndDate
;
LOAD "Entry Exit Client Id",
	[Entry Date] As StartDate,
    [Exit Date] AS EndDate
Resident ClientData
;

With this, you create a table containing the days that a client is active.  You can  then link this table to a master calendar if you so please, or add more attributes to the dates table.

One thing you have to keep in mind is that if you want to count the number of active Ids over a time period, you will have to use DISTINCT in your clause i.e COUNT(DISTINCT [Entry Exit Client Id])

[jbakerstull]

I only have front end user availability. I was thinking maybe it would be easier to return a 1 value if dates overlap.

I created a column and created the following formula " =Date([vStartDate]+1)". What I need to figure out the formula to return a 1 value if "Date([vStartDate]+1)" is overlaps [Entry Date] and [Exit adjusted Date].