The goal of this tutorial is to create a table that will display year over year comparison of values aggregated by months with custom start date. In this case we want to display data from 21st day of previous month to 20th day of actual month.
It is recommended to get familiar with date functions (month, date, dayOfMonth) before proceeding with this tutorial.
Data Set preparation
Two special attributes need to be created in data set:
- Create attribute “Statistical Month” in Data Set with transformation script:
if (month(date(value("L_NAME_442")))==1) { if (dayOfMonth(date(value("L_NAME_442")))>=21){ return "Statistical February" } else { return "Statistical January" } }
This is only a part of the script which is valid only for one month. The whole script can be found in an attachment: statisticalmonths.txt.
The script checks if the month is equal to 1 (January). Then it checks if the day of month is equal or bigger than 21. If it is true, then it is labelled as “Statistical February. If not, it is labelled as “Statistical January”. You can of course use different labels.
The same goes for all other months (only the month number and return value is changing).
- Create attribute “Statistical Year” in Data Set with transformation script:
if (month(date(value("L_NAME_442")))==12) { if (dayOfMonth(date(value("L_NAME_442")))>=21){ return year(plus(date(value("L_NAME_442")), 'years', 1)); } else { return year(date(value("L_NAME_442"))); } } else { return year(date(value("L_NAME_442"))); }
This script returns the year. If the date is bigger than 21st December, then it returns year +1. If not, it returns just the year. This way the “Statistical Year” lasts from last year’s 21st December to 20th December of this year (for example 2013/12/21 – 2014/12/20).
- Create Subsets in Data Set for Months and Years. The Months and Years need to be correctly sorted. Therefore you need to create subsets with correct sorting.
Report
Create view with an indicator of your choice, attribute Statistical Month with selected Subset on x-axis and attribute Statistical Year with selected Subset of y-axis.