Metric Sheet - Data entered during previous month

Lee Ratzlaff

Hello!

We have a project where data is entered for an individual client on a monthly basis. We have created a metric sheet so we can share aggregate data in charts on a dashboard, and have set it up for the two enrollment statuses that only occur once ("referral" and "discharge"); however, we are struggling with how to pull metrics for those records that are entered for youth with the enrollment status of "Active".

Each client enrolled in the grid will have one "Active" enrollment record entered every month. We only want to report those youth who were listed as "Active" during the last reporting period (variable that lists the last day of the month they are reporting, ex. 03/31/21). If we don't bound this, then our metrics will include every single client multiple times.

I was thinking about creating a secondary grid with cell links, but it doesn't look like I can set a criteria with that feature - so it's back to trying to figure out a better countif statement...

Example of the "easy" formula: =COUNTIFS({SOC Wraparound Client Tracker Range 13}, "Juvenile Justice System", {SOC Wraparound Client Tracker Range 10}, "La Casa, Chaves", {SOC Wraparound Client Tracker Range 11}, "Referral")

Thanks!!

Mark Cronk

Hi,

Try adding a range and condition for "active" and one for the report month. You may need to add a reporting month column if you don't have another date column already:

=COUNTIFS({SOC Wraparound Client Tracker Range 13}, "Juvenile Justice System", {SOC Wraparound Client Tracker Range 10}, "La Casa, Chaves", {SOC Wraparound Client Tracker Range 11}, "Referral", {insert enrollment range}, "Active", {insert reporting month range}, month(@cell)=(month(today())-1))

The "month(@cell)=(month(today())-1))" will report last month's entries. You could also hard code the month, e.g. Month(@cell)=1 for January report.

Help?

Mark

Mark Cronk

Hi,

Try adding a range and condition for "active" and one for the report month. You may need to add a reporting month column if you don't have another date column already:

=COUNTIFS({SOC Wraparound Client Tracker Range 13}, "Juvenile Justice System", {SOC Wraparound Client Tracker Range 10}, "La Casa, Chaves", {SOC Wraparound Client Tracker Range 11}, "Referral", {insert enrollment range}, "Active", {insert reporting month range}, month(@cell)=(month(today())-1))

The "month(@cell)=(month(today())-1))" will report last month's entries. You could also hard code the month, e.g. Month(@cell)=1 for January report.

Help?

Mark

Lee Ratzlaff

@Mark Cronk - thanks!! This looks like it's working!

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark