The portal dashboard displays analytics data based on a time range - e.g. it might display the number of assessments performed in the last 12 months.
It also might show the data broken down into a number of partitions (groups) - e.g. to display the assessments performed each month over the last 12 months. The Reporting Partition Generation algorithm will, given a set of parameters, generate one or more partitions (date ranges) that are then used to select and filter data for the client.
See Analytics Reporting Periods Terminology for a definition of the terms used on this page.
A partition describes a date-based subset of data to calculate analytics on. There are three types of partitions:
partition_type |
Description |
Number returned |
Example Usage |
---|---|---|---|
|
All data |
1 |
Return the total number of assessments |
|
Data for the entire reporting period |
1 |
Return the number of assessments created in the last 12 months |
|
Date for a group within the period |
multiple |
Return the number of assessments created in each of the last 12 months |
The partition generation process requires the following parameters:
Parameter |
Description |
Example |
---|---|---|
|
The basic unit of time to report on |
|
|
The number of units of time in each group |
|
|
The number of groups of data to return in total |
|
|
The current date and time. Defaults to the actual date time, but can be earlier if we were trying to report as though we are at an earlier point in time. |
|
|
Indicates if we should return the |
|
|
Indicates if we should return the |
|
|
Indicates if we should return the |
|
Note
See Analytics Reporting Periods Terminology for more details of these parameters
def fetch_partition_dates (period_unit, group_size, number_of_groups, now): # Fetch all of the partition dates for reporting metrics # Returns number_of_groups records # Truncate the date to get the start of the current group period #. e.g. truncate("2022-11-16", month) returns "2022-11-01" #. e.g. truncate("2022-11-16", year) returns "2022-01-01" current_period_start = truncate(now, period_unit) # Get the time interval to subtract from the current_period_start to find the start of the # first group period being returned #. e.g. interval(12, month) returns "12 months" #. e.g. interval(3, year) returns "3 years" interval_to_subtract = interval(group_size * (number_of_groups - 1), period_unit) # Get the period of time to add to move from the start of one period to the start of the next period interval_to_add = interval(group_size, period_unit) group_start = current_period_start - interval_to_subtract period_index = 0 while period_index < number_of_groups: group_end = group_start + interval_to_add; group_end = group_end - '0.000001 SECONDS'; # This period ends a microsecond before the next period starts group_name = # TBD - See ss_reporting_period_groups for details on how then name is formed return group_name, group_start, group_end period_index += 1 def fetch_partitions (period_unit, group_size, number_of_groups, now, return_all_data, return_period_data, return_group_data): # Fetch all of the partitions for reporting metrics # Reutrns 1 or more records, depending on the return_* parameters # Return the single "all" data record # No need for calculations for the "all" group if return_all_data: return "all", "All/Current", null, now # Return the single "period" data record if return_period_data: partition_type = "period" group_name, group_start, group_end = fetch_partition_dates ( period_unit, group_size * number_of_groups, 1, now): return partition_type, group_name, group_start, group_end # Return the multiple "groups" data records if return_period_data: partition_type = "groups" for group_name, group_start, group_end in fetch_partition_dates ( period_unit, group_size, number_of_groups, now): return partition_type, group_name, group_start, group_end
Parameters:
period_unit |
group_size |
number_of_groups |
now |
---|---|---|---|
|
1 |
12 |
16th November 2022 |
Returned partitions:
partition_type |
Group Index |
group_name |
group_start1 |
group_end2 |
returned? |
---|---|---|---|---|---|
|
n/a |
All/Current |
null |
The |
if |
|
n/a |
Dec 2021 - Nov 2022 |
1/12/2021 |
30/11/2022 |
if |
|
1 |
Dec 2021 |
1/12/2021 |
31/12/2021 |
if |
|
2 |
Jan 2022 |
1/1/2022 |
31/1/2022 |
if |
… |
n |
… |
… |
… |
if |
|
12 |
Nov 2022 |
1/11/2022 |
30/10/2022 |
if |
Note
-
group_start
always has a start time of 00:00:00 -
group_end
always has a start time of 23:59:59
Parameters:
period_unit |
group_size |
number_of_groups |
now |
---|---|---|---|
|
1 |
3 |
16th November 2022 |
Returned partitions:
partition_type |
Group Index |
group_name |
group_start1 |
group_end2 |
returned? |
---|---|---|---|---|---|
|
n/a |
All/Current |
null |
As per the |
if |
|
n/a |
2020 - 2022 |
1/1/2020 |
31/12/2022 |
if |
|
1 |
2020 |
1/1/2020 |
31/12/2020 |
if |
|
2 |
2021 |
1/1/2021 |
31/12/2021 |
if |
|
3 |
2022 |
1/1/2022 |
31/12/2022 |
if |
Note
-
group_start
always has a start time of 00:00:00 -
group_end
always has a start time of 23:59:59
When selecting user data, there are three key dates that control the data returned:
-
group_start
- the start date for the reporting period, which could be null -
group_end
- the end date for the reporting period, which could be after thenow
parameter -
now
- the cut-off date - i.e. we only consider data created/modified before this point in time. This value is typically now() (the actual current time), but in some cases, it may be an earlier time (for retrospective reporting)
When selecting data from the database, you should:
-
use 1/1/1980 as the
select_start
date ifgroup_start
is null -
use the current time if
now
is null or not supplied -
use the minimum of the
now
parameter and thegroup_end
value as theselect_end
date
The pseudo-code below shows how you might select the number of assessments for the returned partitions
# Assumes "now" holds the effective now time and "partitions" is a list of the partitions to report on for partition in partitions: # Set the start date for selecting if partition.start_group: select_start = partition.start_group else: select_start = '1980-01-01 00:00:00'; # Set the end date for selecting if not now: now = NOW() select_end = min(partition.end_group, now) # Fetch the data assessments_for_partion = SELECT COUNT(*) FROM assessment_assessment WHERE assessment_date BETWEEN select_start AND select_end