Creating Averages with the Advanced Editor

Updated 10 months ago by Becky

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 %}

<html>

<head>

<style type='text/css'>.box{background-color:white;-webkit-border-radius:5px;-moz-border-radius:5px;border-radius:5px;border:3px solid #d1d1d1}.box.muted{background-color:#f7f7f7}.data-table{margin-bottom:0;border-collapse:separate;width:100%;border-spacing:0;overflow:visible !important}.data-table th{line-height:1.2;padding:8px 10px;vertical-align:middle;background:#cccccc;background-image:-webkit-gradient(linear, 50% 0%, 50% 100%, color-stop(0%, #ddd), color-stop(100%, #d3d3d3));background-image:-webkit-linear-gradient(#ddd, #d3d3d3);background-image:-moz-linear-gradient(#ddd, #d3d3d3);background-image:-o-linear-gradient(#ddd, #d3d3d3);background-image:linear-gradient(#dddddd,#d3d3d3);color:#686868;text-shadow:0 1px 1px white}.data-table thead{text-align:left;font-size:12px;display:table-header-group}.data-table thead th{background:#222222;background-image:-webkit-gradient(linear, 50% 0%, 50% 100%, color-stop(0%, #303030), color-stop(100%, #1e1e1e));background-image:-webkit-linear-gradient(#303030, #1e1e1e);background-image:-moz-linear-gradient(#303030, #1e1e1e);background-image:-o-linear-gradient(#303030, #1e1e1e);background-image:linear-gradient(#303030,#1e1e1e);color:white;text-shadow:none}.data-table thead:first-child{-webkit-border-top-left-radius:3px;-moz-border-radius-topleft:3px;border-top-left-radius:3px}.data-table thead:last-child{-webkit-border-top-right-radius:3px;-moz-border-radius-topright:3px;border-top-right-radius:3px}.data-table thead tr+tr th{background-color:#999999 !important;background-image:none !important;filter:none;-webkit-border-radius:0 !important;-moz-border-radius:0 !important;border-radius:0 !important;color:#333333 !important;text-align:center}.data-table tbody{background:white;border:3px solid #d1d1d1;border-top:none;font-size:12px;line-height:1;text-align:right}.data-table tbody h1{text-align:left;margin-bottom:0}.data-table tbody h2{text-align:left;margin-bottom:0;font-size:16px}.data-table tr{page-break-inside:avoid}.data-table td{border-right:1px solid #dbdbdb;border-bottom:1px solid #dbdbdb;padding:10px}.data-table tbody tr:nth-child(even){background:#f1f1f1}.data-table tbody tr:hover{background:#f4edd6}.data-table.draggable th{background:#222 url(file:////app/public/grabber.png) no-repeat right center;background:url(file:////app/public/grabber.png) no-repeat right center,-o-linear-gradient(top, #333 0%, #111 100%);background:url(file:////app/public/grabber.png) no-repeat right center,-moz-linear-gradient(top, #333 0%, #111 100%);background:url(file:////app/public/grabber.png) no-repeat right center,-webkit-linear-gradient(top, #333 0%, #111 100%);background:url(file:////app/public/grabber.png) no-repeat right center,linear-gradient(top, #333333 0%,#111111 100%);background:url(file:////app/public/grabber.png) no-repeat right center,-ms-linear-gradient(top, #333 0%, #111 100%);cursor:move;padding-right:20px}p{font-family:"proxima-nova", "Helvetica Neue", Helvetica, Arial, sans-serif}.text-muted{color:#9F9FA0}.text-small{font-size:13px}.p-b-1-5{padding-bottom:1.5rem}.hillsong p{margin:0}.hillsong #church-title{color:#9f9fa0;font-size:15px}.hillsong #primary-title{color:#404041;font-size:25px;font-weight:bold}.hillsong #secondary-title{color:#404041;font-size:15px}.hillsong .row-title{color:#404041;font-size:18px;margin:16px 0;font-weight:bold}.hillsong .dials{width:100%;margin-bottom:24px}.hillsong .dials-container{width:704px;margin:auto}.hillsong .dial-separator{width:0;margin:0 24px;display:inline-block}.hillsong-dial{width:96px;height:80px;background-repeat:no-repeat;position:relative;display:inline-block}.hillsong-dial .data{width:100%;position:absolute;bottom:0;display:inline-block;text-align:center;vertical-align:bottom}.hillsong .hr{width:100%;height:1px;background-color:#d8d8d9}.hillsong-dial .no-data{font-weight:bold;color:#9f9fa0;font-size:14px}.hillsong-dial .title{font-weight:bold;color:#404041;font-size:14px}.hillsong-dial .subtitle{font-size:9px;color:#404041}.hillsong-dial .label{margin-top:7px;font-size:9px;color:#9f9fa0}

</style>

</head>

<body>

<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 %}

{% comment %} Set your attendance category here {% endcomment %}

{% assign att1 = week.total_attendance %}

{% if att1 > 0 or include_0_weeks %}

{% assign period_sum_attendance = period_sum_attendance | plus: att1 %}

{% assign period_active_weeks = period_active_weeks | plus: 1 %}

{% endif %}

{% if debug_on %}

<tr>

<th>{{ week.timestamp | date: '%b %d, %Y' }}</th>

<td>{{ att1 | 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>

</body>

</html>

__________________________________________________

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 is one location to edit in the report if you change the category name.


How did we do?


Powered by HelpDocs (opens in a new tab)