Division Comparison Widget

The Division Comparison widget displays a line graph comparing one division’s attendance to another’s over the past twelve weeks. The comparison is represented with the first division’s attendance as a percentage of the second division’s. Up to three years (the current year and the previous two) can be displayed with color-coded lines. Alternatively, the first division can be compare to a fixed number – such as a target attendance or the seating capacity of your facilities.

The widget utilizes an HTML file, SQL script and Python script as shown below. Since the attendance data is the same for all users, Caching should be set to all users.

  • To customize the widget to compare two divisions, you will need to change the division names and IDs on lines 7 & 8 of the Python script. Attendance for the first division will be shown as a percentage of attendance for the second division. No more than two divisions should be entered in the script.

  • To customize the widget to compare a division’s attendance to a fixed number, enter the division name and ID on line 7 of the Python script and the fixed number and description on lines 14 & 15. In addition, you will also need to change the value of displayAttendanceVsAttendance to true.

  • To configure the number of years (lines) to display on the graph, Modify line 11 setting startyear with the first year to include. Only three years can be displayed, so you can select the past year (to compare only last year and this year) or the year before that (to compare the previous two years and the current year).

Video

Below is a short video demonstrating some of the features of this widget.


HTML Code

Below is the HTML code for the Division Comparison widget. As supplied by TouchPoint, the file name is WidgetCurrentRatioByWeekHTML.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
<div class="box">
    <div class="box-title hidden-xs">
        <h5>{{WidgetName}}</h5>
    </div>
    <a class="visible-xs-block" id="giving-fc-collapse" data-toggle="collapse" href="#{{WidgetId}}-section" aria-expanded="true" aria-controls="{{WidgetId}}-section">
        <div class="box-title">
            <h5>
                <i class="fa fa-chevron-circle-right"></i>&nbsp;&nbsp;{{WidgetName}}
            </h5>
        </div>
    </a>
    <div class="collapse in" id="{{WidgetId}}-section">
        <div class="box-content center">
        <div id="tester"></div>
            <div class="chart">
            </div>
        </div>
    </div>
</div>
<script type="text/javascript">
    var divOneData = {{{divonedata}}};
    var divTwoData = {{{divtwodata}}};
    var adjustedDivOneData = [];
    var adjustedDivTwoData = [];
    const startingYear = {{{startingyear}}};
    const startingWeek = {{{startingweek}}};
    const fixedNumber = {{{fixednumber}}};
    const fixedName = '{{{fixedname}}}';
    const DisplayAttendanceVsAttendance = {{{displayattendancevsattendance}}};
    const numYears = {{{numyears}}};
    const numWeeks = {{{numweeks}}};
    var weeklySundays = [];

    var {{WidgetId}} = function() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Name');
        {{{addcolumns}}}

        weeklySundays = GetThisSunday();

        if(DisplayAttendanceVsAttendance == true){
            for(let i = 0; i < 3; i++){
                for(let j = 0; j < 12; j++){
                    let tempHeadCount = 0;
                    let tempWeekCount = startingWeek[0][0] + j;
                    let tempYearCount = startingYear + i;
                    for(let k = 0; k < divOneData.length; k++){
                        if(divOneData[k][0] == tempWeekCount && divOneData[k][2] == tempYearCount){
                            tempHeadCount = divOneData[k][1];
                            break;
                        }
                    }
                    var tempObject = [weeklySundays[j], tempHeadCount, tempYearCount];
                    adjustedDivOneData.push(tempObject);
                }
                for(let j = 0; j < 12; j++){
                    let tempHeadCount = 0;
                    let tempWeekCount = startingWeek[0][0] + j;
                    let tempYearCount = startingYear + i;
                    for(let k = 0; k < divTwoData.length; k++){
                        if(divTwoData[k][0] == tempWeekCount && divTwoData[k][2] == tempYearCount){
                            tempHeadCount = divTwoData[k][1];
                            break;
                        }
                    }
                    var tempObject = [weeklySundays[j], tempHeadCount, tempYearCount];
                    adjustedDivTwoData.push(tempObject);
                }
                for(let i = 0; i < adjustedDivOneData.length; i++){
                    if(adjustedDivTwoData[i][1] != 0){
                        adjustedDivOneData[i][1] = (adjustedDivOneData[i][1] / adjustedDivTwoData[i][1])*100;
                    } else{
                        adjustedDivOneData[i][1] = 0;
                    }
                }
            }
        } else{
            for(let i = 0; i < 3; i++){
                for(let j = 0; j < 12; j++){
                    let tempHeadCount = 0;
                    let tempWeekCount = startingWeek[0][0] + j;
                    let tempYearCount = startingYear + i;
                    for(let k = 0; k < divOneData.length; k++){
                        if(divOneData[k][0] == tempWeekCount && divOneData[k][2] == tempYearCount){
                            tempHeadCount = divOneData[k][1];
                            break;
                        }
                    }
                    tempHeadCount = (tempHeadCount / fixedNumber)*100;
                    var tempObject = [weeklySundays[j], tempHeadCount, tempYearCount];
                    adjustedDivOneData.push(tempObject);
                }
            }
        }

        switch(numYears){
            case 1:
                for(let i = 0; i < numWeeks; i++){
                    let year1 = i;
                    data.addRow([adjustedDivOneData[i][0].toString(),adjustedDivOneData[year1][1]]);
                }
                break;
            case 2:
                for(let i = 0; i < numWeeks; i++){
                    let year1 = i;
                    let year2 = i + numWeeks;
                    data.addRow([adjustedDivOneData[i][0].toString(),adjustedDivOneData[year1][1], adjustedDivOneData[year2][1]]);
                }
                break;
            case 3:
                for(let i = 0; i < numWeeks; i++){
                    let year1 = i;
                    let year2 = i + numWeeks;
                    let year3 = i + (numWeeks * 2);
                    data.addRow([adjustedDivOneData[i][0].toString(),adjustedDivOneData[year1][1], adjustedDivOneData[year2][1], adjustedDivOneData[year3][1]]);
                }
                break;
            default:
                break;
        }

        var options = {
            hAxis: {
            title: 'Dates'
            },
            colors: ['#CC0300', '#5CA12B', '#345CAD', '#F9B710', '#9E4EBC', '#74746D'],
            vAxis: { format: '#\'%\'' },
            hAxis: {
                slantedText: false,
                textStyle: {
                    fontSize: 10
                }
            },
            legend: {
                position: "top",
                textStyle: {fontSize: 10},
                alignment: 'center'
            },
        };

        var chart = new google.visualization.LineChart(document.querySelector('#{{WidgetId}}-section .chart'));
        chart.draw(data, options);
    }

    function GetThisSunday(){
        var thisSunday = new Date();
        for(let i = 0; i < 7; i++){
            if(thisSunday.getDay() != 0){
                thisSunday.setDate(thisSunday.getDate() + 1);
            } else{
                break;
            }
        }
        var sundays = [];
        thisSunday.setDate(thisSunday.getDate() + 7);
        for(let i = 11; i >= 0; i--){
            thisSunday.setDate(thisSunday.getDate() - 7);
            let day = thisSunday.getDate().toString();
            let month = thisSunday.getMonth() + 1;
            month = month.toString();
            sundays[i] = month + '/' + day;
        }
        return sundays;
    }
    // load and register the chart
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback({{WidgetId}});
    WidgetCharts.{{WidgetId}} = {{WidgetId}};
</script>

SQL Script

Below is the SQL script for the Division Comparison widget. As supplied by TouchPoint, the file name is WidgetCurrentRatioByWeekSQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
set datefirst 1;
with weeks as (
select DATEPART(WEEK, GETDATE()) w UNION ALL
select DATEPART(WEEK, GETDATE() - 7) w UNION ALL
select DATEPART(WEEK, GETDATE() - 14) w UNION ALL
select DATEPART(WEEK, GETDATE() - 21) w UNION ALL
select DATEPART(WEEK, GETDATE() - 28) w UNION ALL
select DATEPART(WEEK, GETDATE() - 35) w UNION ALL
select DATEPART(WEEK, GETDATE() - 42) w UNION ALL
select DATEPART(WEEK, GETDATE() - 49) w UNION ALL
select DATEPART(WEEK, GETDATE() - 56) w UNION ALL
select DATEPART(WEEK, GETDATE() - 63) w UNION ALL
select DATEPART(WEEK, GETDATE() - 70) w UNION ALL
select DATEPART(WEEK, GETDATE() - 77) w
),
attendance as (
select weeks.w, SUM(COALESCE(HeadCOUNT,0)) as HeadCount, YEAR(m.MeetingDate) as MyYear from weeks
cross join dbo.Organizations o
left join dbo.Meetings m on DATEPART(WEEK, m.MeetingDate) = weeks.w and o.OrganizationId = m.OrganizationId and YEAR(m.MeetingDate) >= YEAR('@startYear')
where o.DivisionId in (@divId)
group by weeks.w, YEAR(m.MeetingDate)
)
select * from attendance
order by w;

Python Script

Below is the Python script for the Division Comparison widget. As supplied by TouchPoint, the file name is WidgetCurrentRatioByWeekPython.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
# Users can add divisions to include in the ratio chart for this widget inside this divisions array.
# Only two divisions can be selected. More than that will cause issues.
# This array is made up of two objects. Each object is a division. Each object contains a name and a division id number.
# The first item in the object is the name you wish to display in the widget for the selected division.
# The second item in the object is the division id you wish to select for this widget.
divisions = [
    ['Young Couples', 210],
    ['Young Marrieds', 211]
]
# You can change startyear to the date you wish to start the chart for your widget. Notice, the furthest back you can select is 3 years.
startyear = '2018'

# Here you can set a fixed number and a name for this fixed number. This will be included in the ratio line graph for each year selected.
fixedNumber = 500
fixedName = 'Church Seating Capacity'

# The ratio for this graph will be based on a comparison between the first division in the array above and the fixed number chosen above.
# To change this to a comparison between two divisions in the array above, change this setting to true
displayAttendanceVsAttendance = 'false'

# Do not adjust the number of weeks to display, as display issues may occur.
numWeeks = 12

import datetime

currentDate = datetime.datetime.now()


def GetData(divid, startingYear):
    sql = Data.SQLContent
    sql = sql.replace('@startYear', str(startingYear))
    sql = sql.replace('@divId', str(divid))
    return q.QuerySqlJsonArray(sql)


def Get():
    sql = Data.SQLContent
    template = Data.HTMLContent
    startingYear = int(startyear)
    maxYears = currentDate.year - 2
    if startingYear < maxYears:
        startingYear = maxYears
    numYears = currentDate.year - startingYear + 1
    selectedStartingYear = startingYear
    selectedYears = []
    while selectedStartingYear <= currentDate.year:
        selectedYears.append(selectedStartingYear)
        selectedStartingYear += 1
    addcolumn = "data.addColumn('number', '{}');"
    alist = [addcolumn.format(x) for x in selectedYears]
    Data.addcolumns = '\n'.join(alist)
    Data.startingyear = startingYear
    Data.startingweek = q.QuerySqlJsonArray('select DATEPART(WEEK, GETDATE() - 77) w')
    Data.numyears = numYears
    Data.numweeks = numWeeks
    Data.divonedata = GetData(divisions[0][1], startingYear)
    divLength = len(divisions)
    if divLength > 1:
        Data.divtwodata = GetData(divisions[1][1], startingYear)
    else:
        Data.divtwodata = 0
    Data.displayattendancevsattendance = displayAttendanceVsAttendance.lower()
    Data.fixednumber = fixedNumber
    Data.fixedname = fixedName
    print model.RenderTemplate(template)


Get()


Latest Update

08/12/2020

Added video.