Hi @Kirstyp!
Okay, take a look at the attached. There is a LOT going on here, so I'll summarize what is happening for each objective, but it will make the most sense if you take a look at each tool to start to get familiar with the capabilities and how they can be used...
Added a few tools at the beginning to make life easier for later calculations, including an initial Summarize tool to group all fields (thereby eliminating any duplicates, which appeared to find a few), filtering out entries that had a null value for Placement Reason, calculate starting & ending Fiscal Year value for the year based on start and end date (using today's date if there isn't an end date), and then generating rows between the starting fiscal year date and the ending fiscal year date. Following are some options for calculating each of the objectives identified in the tab in your sample data...
1. People in Residential vs. Nursing care each year: Filter for ActiveFiscalYear >= 2014 > Summarize tool (Group by ActiveFiscalYear & Service Type, Count Distinct customer ID's) > Cross-Tab to put the years as headers and show count per Service Type.
2. # of Long Term/Short Term admission per year: Identify Short vs. Long using RegEx tool (there are many other ways to do this part, including string formulas) > Multi-Row formula to check if moved from Short to Long/vice versa and if the end date of the prior row is equal to the start date of the current row (which would indicate no gap in service) > Filter out "NEW" admissions > Summarize tool (Group by ActiveFiscalYear, Count Distinct customer ID's)
3. Total deceased & deceased per year: Filter for Date of Death <> Null and Date of Death = SER Actual End Date > Summarize tool (Group by Fiscal Year End, Count Distinct customer ID's). Join to a Summarize tool result of the Count Distinct customer ID's with no grouping, then appended to the # deceased per year from the above branch.
4. Number of bed nights per client: Formula tool to calculate date difference between start & end date > Summarize tool (Group by customer ID, sum Bed Nights)
5. Age at admission: Summarize tool (Group by customer ID & DOB, minimum Start Date) > Formula tool to calculate date difference in years between DOB & Start Date of admission.
This example you've provided is actually a great example of the flexibility of the Summarize tool, as it was used in every one of the parts of this suggested solution. Please take a look at the attached example, dissect the pieces, and let me know if you have any questions! (Feel free to message me privately if you'd prefer!)
I hope this helps get you started on this path to realizing the potential of this program - there are probably a hundred different ways to calculate the items you are wishing to calculate - this is just one of many! But there should be some good examples in here of the types of calculations & procedures you can perform... so let me know if you run into stumbling blocks with this, or if the workflow needs additional tweaking!
Cheers,
NJ
