Previous months point in time

[dhborchardt]

I have a table that shows admissions and discharges into the hospital. These are broken down into each day. So for example if a patient was in the hospital for 3 days this visit is broken into three records. The chart counts the census for the month. That part seems to work fine.

Now I want to show where I was on previous months according to todays date. In the attached example there are three full months and up to the 11th day in April. I want a second bar to appear showing where I was on the 11th day in Jan, Feb, and March.

I started playing around with InMonthToDate but I am getting a syntax error and as usual there is probably a better way to do it (I am still using training wheels).

[#2 · Accepted solution]

Hello

I think what you need is a new field, let's call it CurDay, created during load similar to:

day(Date) AS CurDay

Values are from 1 to 31. Then use that field in the expression lik this:

Count ({$<InpatientServiceID={MED}, CurDay={"$(= '<=' & day((today())))"}>} MonthYear)

HtH,

Roland