Create a Week of the Year Report

Use a week-of-the-year report to group records by the week number associated with a date field. This pattern can help you compare recurring activity across the year, such as event registrations, application creation, or person record creation.

This example uses a report on the Form Response base to group recurring event activity by year and week.

   

       

⭐ Get Inspired

   

   

This article was adapted from a post by Technolutions staff in the Slate Community Forums' Get Inspired space. Have a great idea for a Get Inspired post? Let us know!

When to use a week-of-the-year report

A week-of-the-year report can help you identify patterns in activity that repeats throughout the year. For example, you can use this report to:

• Measure weekly activity for recurring events, such as campus tours or group tours.

• Compare larger recurring events, such as scholar days, college fairs, alumni engagement events, or high school visits.

• Identify peak weeks for record creation or application creation.

Creating the report

1. Create a report on the Form Response base.

2. Filter the report to include the relevant event responses. For example, configure the following filters:

   • Type: IN Event.

   • Folder: IN the folder that contains the events that you want to report on.

   You can also filter by event template or another event attribute if that better matches your reporting goal.

3. Add the columns needed for the metrics that you want to review, such as overall registered, attended, canceled, no shows, and attendance rates.

For more information about attendance rate calculations, see Formulas in Reports.

Grouping the report by year and week

In the Data Table, add the joins and exports that create the year and week groupings.

1. Add a join to Form.

2. Export the form folder or template. Use the option that matches how you want to group or filter your events.

3. Export Start Date/Time.

4. Set Format Type to Date.

5. Set Format Mask to yyyy. This groups the events by year.

6. Add a Subquery Export.

7. Set Output to Concatenate.

8. Add a literal value of Week . Include the trailing space after the word Week.

9. Add a nested Subquery Export with an output of Formula.

10. Export Form Start Date/Time in the nested subquery export.

11. Use the following formula:

    format(datepart(wk, @Form-Start-Date-Time), '00')

Understanding the week formula

The formula returns the week number from the exported form start date and formats the result as two digits.

• datepart(wk, @Form-Start-Date-Time): Returns the week of the year from the form start date and time.

• format(..., '00'): Formats the week number as two digits. For example, week 1 appears as 01.

Using two digits keeps the report output easier to scan and sort.

Reviewing the output

After you configure the year and week exports, render the report and confirm that each row groups the expected event activity.

Adapting the report for another date field

You can use the same pattern with other report bases and date fields. For example, a report on the Person base can group person records by the week of the year from Record Created Date.

When adapting the report, replace the date export in the formula with the export key for the date field that you want to analyze.