Looping through to get previous record count

You can group on the date field (for each day)
The count is just a summary (count) of the appropriate field

SInce there are just 2 days the average is just the overall count divided by 2

Sum({PatientID Field}) / 2

If they can enter a random number of days, so long as it is a multi-value parameter the average is just

Sum({PatientID Field}) /  UBound({?DateParameter})



mlmcc

Hi mlmcc,
Thanks for your help.
I get an error:
"An array required here" when I write this formular:

{@PatCensus}/Ubound({?Start Date})


 and sum({@PatCensus})/Ubound({?Start Date})
gets an error this field ({@PatCensus}) cannot be summarized.

IS the start date parameter a multi-value parameter?
Did you enter 2 or more dates?

What is the @PatCensus formula?

mlmcc

The Start Date parameter will take only 1 date. @PatCensus formula is the Crystal formula expression for the count of patient ID. If I drop it on the report, it gives me the total count of patients.
Thanks

YOu can't summarize a summary formula.  Just use Count({Patient ID}) for the total patient count

How are you getting 2 dates if the date parameter only has 1 value?

mlmcc

I have nothing new to add to this problem. It's still unsolved. What I need is how to display census total for everyday in the fiscal year (10/1/2017- 09/30/2018). I can get the accurate count for today or any day but the question is how do I display all record counts for each day at the same time?

Thanks,

I can think of 2 ways.

Use a cross tab though may not be useful in this case

Second, Group by DATE set to FOR EACH DAY
Put a summary count in the group header

mlmcc

Thanks mlmcc for your help. I tried to do the second option you provided but that returned the count for each of the days, instead of the sum total of all records. I have attached a picture of what my report looks like. The values in the crooked square is the individual results for each day when I group according to your instruction. The total at the bottom is what I want to place for 2/5/2018 because it is the total count of patients in our care that day. I need a formula that will place the census total for each day, not the number of patients who were admitted that day but the overall.

Thanks again.

I don't see anything attached to your last post.  Did you enter a description for the file?  I think you have to do that for the file to be attached.

 I haven't posted here before, but I've been following the conversation, and I think I finally understand what you're looking for.  Going back to your first post, you showed counts for 01/01/2018 and 01/22/2018.  As I understand it, those were the number of people in the facility on those dates (as opposed to something like the number of people admitted on those dates).

 The problem with trying to use a group to get counts like that is that each record can only be included in one group, so anyone that was there on both of those dates will only be included (ie. counted) in the first group.

 If the report was only going to be looking for a few dates at a time (like the 2 in your first post), then you could use formulas to get those counts.  I assume that you have fields for the admittance and release dates.  Then the formulas might be something like:

// first_date_count
if #01/01/2018# in {admittance date field} to {release date field} then
  1
else
  0

// second_date_count
if #01/22/2018# in {admittance date field} to {release date field} then
  1
else
  0

 Then a summary on those formulas would give you a count for each date.  The hard-coded dates (#01/01/2018# and #01/22/2018#) would be replaced by something that was based on your parameter(s).


 In one of your last posts, you mentioned getting a count for every day in the fiscal year.  You could do that using formulas similar to the ones above, but handling that many dates (365) would be cumbersome, at best.  The simplest solution might be to create a table that had every day of the year in it, and then join your patient table to that.  Then you could group on the dates from the date table and get a count for each day.  You couldn't link the date and patient tables directly.  You'd have to handle that using the record selection with something like

{date table field} in {admittance date field} to {release date field}

 There are other ways that you could handle it.  Those are just a couple to get things started.

 James

Hi James,
First, I want to thank you for taking time to look at the issue. I'm very happy also that you truly understand my situation. The way you described it is exactly what I'm facing. The solution proffered seems correct but is giving me zero(0):

If date('10/01/2017') in (date({DATE_DIMENSION.CALENDAR_DT})) to date({@End Date}) then {@CountRec} else 0 .
Thanks

HIs solution will work but you need a more general one since you will be changing the date range.  I think it can be done using arrays.  How long a period would you want to look at?  Would you ever need to look at the counts for 3 years?

mlmcc

Hi mlmcc,
No. At most 1 year. I will have 3 versions of the report; 6 weeks, 3 months and 1 year or year to date.

Thanks.

What are DATE_DIMENSION.CALENDAR_DT, {@End Date} and {@CountRec} ?

 I'm just making some guesses here, but if you're trying to get a count for 10/01, and CALENDAR_DT is the date admitted, and {@End Date} is the release date, then you should just use 1 instead of {@CountRec}:

If date('10/01/2017') in (date({DATE_DIMENSION.CALENDAR_DT})) to date({@End Date}) then 1 else 0

 Then a summary on that formula should give you the count for 10/01.

 James

Hi James,

I'm still working on it but you have thrown a lot of light to it. The DATE_DIMENSION.CALENDAR_DT is from a table that has all dates. Although I'm asked to show records from 10/1/2017 (the start of fiscal year) to date, they want me include the total count as of 9/30/2017. In other words, if my count for 10/1/2017 = 1 and the total record count for 09/30/2017 = 1269, the report should show 1270 for 10/1/2017. That is what I was trying to accomplish by using  {@CountRec} but it's not working.

Thanks,

Is that the count on 09/30, or some kind of total for a period (eg. the prior fiscal year) that ends on 09/30?

 Like I asked before, what are {@End Date} and {@CountRec}?  Checking to see if a specific date (10/01) is between a date in your list of dates (CALENDAR_DT) and {@End Date} doesn't seem to make much sense, but without knowing what's in {@End Date} and {@CountRec}, I don't know what you're actually trying to do there, much less why it might not be working.

 James

Hi James,
Below is the date range I'm using. {HH_EPSD_EVNT_DATES.DATE_OF_EVENT} is the admission date and ({@Start Date} is the day of the count.  {@End Date} is less important because I want to see every record prior to and including the {@End Date}. {@CountRec} is the count before 10/1:

({HH_EPSD_EVNT_DATES.DATE_OF_EVENT} <=date({@Start Date}) AND {HH_EPSD_EVNT_DATES.DATE_OF_EVENT} <= date({@End Date})
)
and  {DATE_DIMENSION.CALENDAR_DT} >= {?FYBegin}
and {DATE_DIMENSION.CALENDAR_DT}<={?FYEnd}

Thanks.

Is that your record selection formula?  I'm guessing that it is.

 If you're trying to get a count of people in the facility on a given date, then I assume that there is also a release date.  You need to check that date in your record selection formula, to "link" DATE_DIMENSION and HH_EPSD_EVNT_DATES.  The idea is to get your patient records (presumably HH_EPSD_EVNT_DATES) included once for each date in DATE_DIMENSION when they were in the facility (ie. the date is in between their admittance and release dates).

 So, I think the record selection formula would be:

{DATE_DIMENSION.CALENDAR_DT} >= {?FYBegin}
and {DATE_DIMENSION.CALENDAR_DT}<={?FYEnd}
and {DATE_DIMENSION.CALENDAR_DT} in {HH_EPSD_EVNT_DATES.DATE_OF_EVENT} to {release date field}

 That limits the dates to the fiscal year, and then "links" the corresponding patient records to each of those dates.

 Then if you group on CALENDAR_DT, for each day, and do a simple count, you should get the patient count for each day.  Assuming that I've correctly understood your data.

 One possible wrinkle is if your dates are actually date times, and include a time other than 12 AM.  Then you may need to include some "adjustments", like converting the datetimes to dates, to remove the time element from the comparison.


 You said "{@CountRec} is the count before 10/1".  I didn't try to incorporate that, because I'm not sure what you're trying to count there.  If the main part of the report is producing a count for each day in the fiscal year, what would a count for the period of time before that be?  Just the count for the day before that (9/30)?  Or for all (?) of the days before that?  Or some kind of average?

 If it's some kind of total or average for a period of time before 10/01, a subreport might be the way to go.  The subreport would read those older records and produce your total/average.  Then the main report could just deal with the requested fiscal year.

 James

Hi James,

I was away for a couple of days. I will try all of these suggestions tomorrow.
Thanks for your help