To analyze data over relative time ranges, use calendar measures. For example, you might want to compare current year-to-date sales figures with figures from the same period the previous year.
Calendar measures aggregate data from a field over a time range, and are saved in the Measures category in the master items. Calendar measures comprise a field to be aggregated, an aggregation, a date field, and a time range for that date field that sets which data is included in the aggregation. You create calendar measures under Fields in the Assets panel, using the Create calendar measures dialog.
You can aggregate fields from tables loaded in Data manager or from a script in Data load editor, as long as the field is in the same table as the date field, or is in an associated table.
However, the date field must be from a table that has been loaded using Data manager, because calendar measures use expressions tagged as date & time fields that are declared in autoCalendar, and date fields are only mapped to autoCalendar when loaded in Data manager. Calendar measures support the following aggregations with the aggregated field: Sum, Count, Avg, Min, and Max.
Calendar measures do not support calendars created using the Data load editor. If you use calendars created using Data load editor and want to create calendar measures, you must load a table containing a date field using Data manager for use with your tables loaded using Data load editor.
If your date field is subject to more than one calendar and both calendars are qualified for use with calendar measures, then the first calendar loaded in the data load script is used in the calendar measures.
A calendar measure can use one of the following time ranges are available for use with calendar measures: weekly, monthly, quarterly, and yearly. Within each time range, different measures exist for periods such as current month, year-to-date, and current week last year. The following time ranges and measures are available for creating calendar measures:
| Measure | Description |
|---|---|
| YTD | The year to date for all years. |
| YTD Current Year | The year to date for the current year. |
| YTD Last Year | The year to date for last year. |
| Measure | Description |
|---|---|
| MTD | The month to date for all months and years. |
| MTD Current Month | The month to date for the current month. |
| MTD Last Month | The month to date for last month. |
| Current Month | All dates this month. |
| Current Month Last Year | All dates this month last year. |
| Last Month | All dates last month. |
| Measure | Description |
|---|---|
| QTD | The quarter to date for all years. |
| QTD Current Quarter | The quarter to date for the current quarter. |
| QTD Last Quarter | The quarter to date for the last quarter. |
| Current Quarter | All dates in the current quarter. |
| Current Quarter Last Year | All dates in the current quarter last year. |
| Last Quarter | All days for the last quarter. |
| Measure | Description |
|---|---|
| WTD | The week to date for all weeks across all years. |
| WTD Current Week | This week to date for the current week. |
| WTD Last Week | The week to date for the last week. |
| Current Week | All dates this week. |
| Current Week Last Year | All dates this week last year. |
| Last Week | All dates last week. |
Once created, calendar measures are treated identically to master measures. That is, calendar measures are reusable and editable while an app is unpublished. Users of a published app will have access to the calendar measures, but will not be able to modify them.
Creating a calendar measure from a field
On a sheet in edit mode with the Fields tab open, do the following:
-
Right-click a field and select Create calendar measures.
By default, the field you right-clicked will be included as the Aggregated field.
- Select a date field from the Date field drop-down list.
-
Select a field from the Aggregated field drop-down list.
- Select an aggregation from the Aggregation drop-down list.
-
Select a time range from the Time range drop-down list.
A list of the available measures displays under Preview of measures.
You can toggle the display of the measures' expressions by selecting the Preview of measures switch.
-
Select the calendar measures to add to your master items.
By default, all measures are selected.
- Click Save to master items.
- Click Close.