Every client will have a week commencing dimension under the time folder:
This configuration in the report engine will configure the dimension used to break down reports. However, you need to also align the report week in the Admin Server.
If you do not have access to the admin server, contact a member of the Bright Analytics team.
Report Engine Configuration
The alterations needed are to the "SQL" and "Use Date Format" sections within the 'Settings' tab
You would need to assure that the "Use Date Format" option is set to unchecked as the date format is being used in the SQL code to set the week commencing.
Now depending on the week required you will need to copy and paste the code into the "SQL" section.
Use the options below:
Saturday - Friday
CONCAT ('w/c ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})), 7)),'%Y-%m-%d (%a)'))Friday - Thursday
CONCAT ('wc ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})+1), 7)),'%Y-%m-%d (%a)'))Thursday - Wednesday
CONCAT ('wc ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})+2), 7)),'%Y-%m-%d (%a)'))Wednesday - Tuesday
CONCAT ('wc ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})+3), 7)),'%Y-%m-%d (%a)'))Tuesday - Monday
CONCAT ('wc ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})+4), 7)),'%Y-%m-%d (%a)'))Monday - Sunday
CONCAT ('wc ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})+5), 7)),'%Y-%m-%d (%a)'))Sunday - Saturday
CONCAT ('wc ',DATE_FORMAT(FROM_DAYS(TO_DAYS({DATE_SQL}) - MOD ((TO_DAYS({DATE_SQL})+6), 7)),'%Y-%m-%d (%a)'))
Once these two changes are made you can commit changes.
How The Code Works?
First, some context:
{DATE_SQL} is a token to get the current date.
TO_DAYS( ) obtains the number of days since '0000-01-01'.
I.E: TO_DAYS('0000-01-10') = 10
FROM_DAYS( ) returns the date against the date value.
I.E: FROM_DAYS(366) = '0001-01-01'
MOD( x , y ) returns the remainder of x after being divided by y.
I.E: MOD( 7 , 2 ) = 1 as 2 fits into 7 three times and leaves a remainder of 1.
The Methodology
Every date can be expressed as a number, we use TO_DAYS to do this. We require the numerical value of dates to provide a platform for us to use number theory.
A week contains seven days, so by obtaining the modulus 7 of a date, it will tell us if the date is at the start of the week ( if it has no remainder - 0 ) or not. Moreover, if the modulus is 1 -6, this tells us how far it is from the start of the week.
We can adjust the week start by adding 1 to 6 to the modulus.
We then obtain the date value for the current date and minus the modulus of the current date. If the date is the first day of the week the modulus will return 0 and so we would obtain the first day of the week. If the date is the second day of the week the modulus will return 1 and take 1 from the current day to give us the first day of the week - and so on.
The CONCAT and DATE_FORMAT are there to express the date value as we desire.