Creating averages with the advanced editor

Last Updated: Jul 07, 2017 02:51PM CDT
This is walk you through creating an average report with the Advanced Editor. This requires some understanding of programming.

First create a parent category to sum attendance for the desired sub-categories.
​​​​
Next create a new report and save it.
​​
Edit the report.  Select all the text in the report.  Delete it and replace it with the following code:
______________________________________________________
{% assign debug_on = false %}
{% assign include_0_weeks = false %}
<table class='data-table box'>
<thead>
<tr>
<th></th>
<th>Avg Weekly Attendance</th>
{% if debug_on %}
    <th>Date(db)</th>
    <th>Sum(db)</th>
    <th>Weeks(db)<th>
{% endif %}
</tr>
</thead>
<tbody>
{% for campus in campuses %}
    <tr>
    <td colspan='2'>
    <h2>{{ campus.name }}</h2>
    </td>
    </tr>
    {% assign period_sum_attendance = 0 %}
    {% assign period_active_weeks = 0 %}
    {% assign period_name_prev = "None" %}
    {% for week in campus.weeks %}
        {% assign period_name_current = week.timestamp | date: '%B %Y' %}
        {% if period_name_prev != period_name_current %}
            {% comment %} *If we just transitioned to a new period, then we need to print the results from the previous period* {% endcomment %}
            {% if forloop.first == false %}                    {% comment %} *Don't print if this is the first pass through.* {% endcomment %}
                <tr>
                    <th>{{ period_name_prev }}</th>
                    {% if period_active_weeks > 0 %}
                        <td>{{ period_sum_attendance | divided_by: period_active_weeks | round: 0 | formatted_number }}</td>
                    {% else %}
                        <td> 0 </td>
                    {% endif %}
                </tr>
            {% endif %}
            {% comment %} *Reset counters on period transition* {% endcomment %}
            {% assign period_sum_attendance = 0 %}
            {% assign period_active_weeks = 0 %}
        {% endif %}
        {% if week.total_attendance > 0 or include_0_weeks %}
            {% assign period_sum_attendance = period_sum_attendance | plus: week.total_attendance %}
            {% assign period_active_weeks = period_active_weeks | plus: 1 %}
        {% endif %}
        {% if debug_on %}
            <tr>
                <th>{{ week.timestamp | date: '%b %d, %Y' }}</th>
                <td>{{ week.attendance | formatted_number }}</td>
                <td>{{ period_name_current }}</td>
                <td>{{ period_sum_attendance | formatted_number }}</td>
                <td>{{ period_active_weeks | formatted_number }}</td>
            </tr>
        {% endif %}
        {% if forloop.last %}
            {% comment %} *Not a period transition but this is the last record, so print result* {% endcomment %}
            <tr>
                <th>{{ period_name_current }}</th>
                {% if period_active_weeks > 0 %}
                    <td>{{ period_sum_attendance | divided_by: period_active_weeks | round: 0 | formatted_number }}</td>
                {% else %}
                    <td> 0 </td>
                {% endif %}
            </tr>
        {% endif %}
        {% assign period_name_prev = period_name_current %}
    {% endfor %}
    {% if period_active_weeks == 0 %}    {% comment %} *No date for this period, print filler message* {% endcomment %}
            <tr>
                <th> No Data </th>
                <td> 0 </td>
            </tr>
    {% endif %}
{% endfor %}
</tbody>
</table>
______________________________________________________

Notes:
The report above is by month, and is determined by this line:
{% assign period_name_current = week.timestamp | date: '%B %Y' %
}
For a report by year, change the line to:
{% assign period_name_current = week.timestamp | date: '%Y' %}

The variable name for the category "Total_Attendance" is "week.total_attendance".  There are two locations to edit in the report if you change the category name.

If you need any additional help, please email support@churchmetrics.com or visit support.churchmetrics.com.