Skip to Content

Creating a date sensitive census

Mar 08 at 12:15 AM


avatar image

I am trying to make an hourly census of visits from our database. I currently have a working version, but it will only run for one day and it doesn't take into account the date which is causing me issues. Currently, I have (this specific codes counts the census at 1AM):

Code:=Sum(If([ED Arrival (Hour of Day)] = 100) Or ([ED Departure (Hour of Day)] = 100) Or ([ED Arrival (Hour of Day)] < 100) And ([ED Departure (Hour of Day)] > 100) Then 1 Else 0)

I have made 24 measures like the above and this works.
This is counting anyone with an arrival or departure of the date I have set, but if they depart the next day or arrive the day before it will not count them right now because it is looking for both times. I am now working to include those that arrive before midnight and leave after midnight the next day, however, I am running into trouble.

1. in my query I am setting 1 date to run, I need to expand this to include the other dates when people arrive (this much I know)
2. I attempted to create a date dimension where I then would input the actual day I want to run by just making the dimension =2/28/18 (this is not working and I think is where I am stuck.)
3. I then wrote a code of:

Code:=Sum(If(([ED Arrival (Hour of Day)] = 100) And ([ED Arrival (Date)] = [qdate])) Or (([ED Departure (Hour of Day)] = 100) And ([ED Departure (Date)] = [qdate])) Or (([ED Arrival (Hour of Day)] = 100) And ([ED Arrival (Date)] = RelativeDate([qdate];-1))) Or (([ED Departure (Hour of Day)] = 100) And ([ED Departure (Date)] = RelativeDate([qdate];+1))) Then 1 Else 0)

[qdate] is my dimension for the date I am trying to run.
Any ideas on how to clean this up? Am I on the right track for including the people who cross the date lines?

10 |10000 characters needed characters left characters exceeded

can you please share the data screen will give the more idea to everyone to understand your formula.


Here is a shot of the data and what I have laid out so far.

capture.jpg (74.1 kB)
* Please Login or Register to Answer, Follow or Comment.

0 Answers