Metric Sheet - Data entered during previous month

Options

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!!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Lee Ratzlaff
    Options

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!