Automatic weekly update

AlixGustav

I am looking to have an automatically updated count for cases opened each week on a dashboard.

Relevant data:

- Date Consent Turned In To CBH]

- [Member Goals] "Obtaining Consents"

- I have a weekly identifier column [Week Consent Turned In To CBH] =WEEKNUMBER([Date Consent Turned In To CBH]@row) if that helps at all

Thanks,

Alix

prime_nathaniel

@AlixGustav this is a common use case for reports where you set your group to "week consent turned in to CBH".

If you absolutely want to do this on a sheet instead of a report, you can do a column of week numbers and a column of years. Add a year column to your original data set, then cross reference and countif.

Paul Newcome

I would also suggest a report to start, but if you want the calcs in another sheet, you wouldn't need any helper columns in the source sheet. Just a manually entered week number and year column in the metrics sheet. You can reference the date in the source sheet directly.

=COUNTIFS({Member Goals}, @cell = "Obtaining Consents", {Date Column}, AND(IFERROR(WEEKNUMBER(@cell), 0) = [Number Column in Metrics Sheet]@row, IFERROR(YEAR(@cell), 0) = [Year Column in Metrics Sheet]@row))

AlixGustav

Thanks. I don't want to do this in a sheet. I mentioned I already have the column for week numbers if that is helpful for moving the rest forward. I would think a report would be better to be able to see it on a dashboard.

Paul Newcome

In that case you would create a row report filtered by the goals criteria and then grouped by the week number column. You could then use the Summarize feature to get your counts by week number.

AlixGustav

Got it, thanks

Paul Newcome

Happy to help. 👍️