How do you change the fiscal calendar? Our company has a fiscal calendar that is different than what is defined in Metric Insights. I can't seem to find a Fiscal Calendar page in which to make these changes.

The fiscal calendar in Metric Insights is not yet available through the UI (to be surfaced in a future release). However, we can still make changes to the calendar directly in the database. To proceed, please follow the instructions below.

1.) Set up a Fiscal Calendar:

You can use the SQL code below if you obtain or create a csv file with the following columns saved as fiscal_master.csv (csv file must be in the following format):

fiscal_month_id:201601

period_name:F16 P01 (MAY)

period_number: 1

first_day_of_period: 2015-04-25

last_day_of_period: 2015-05-29

fiscal_quarter_id: 201601

quarter_name: Q1 FY2016

quarter_number: 1

first_day_of_quarter: 2015-04-25

fiscal_year_id: 2016

year_name: FY2016

first_day_of_fiscal_year: 2015-04-25

Note the sample entry for each column above. Some of the columns can be computed from other columns. This is not a minimal set, just a simple one that works.

2.) Dump the existing fiscal calendar tables as a backup (you'll need root acces):

~# mysqldump dashboard fiscal_month > /tmp/fiscal_month.sql

~# mysqldump dashboard fiscal_quarter > /tmp/fiscal_quarter.sql

~# mysqldump dashboard fiscal_year > /tmp/fiscal_year.sql

3.) Now, create a fiscal master table to import the csv:

**fiscal_master**

>create database temp;

>use temp;

>create table fiscal_master(

fiscal_month_id int,

period_name varchar(20),

period_number int,

first_day_of_period date,

last_day_of_period date,

fiscal_quarter_id int,

quarter_name varchar(9),

quarter_number int,

first_day_of_quarter date,

fiscal_year_id int,

year_name varchar(6),

first_day_of_fiscal_year date

);

4.) Load the fiscal_master table from your csv file to the various fiscal tables:

 

MySQL command to load the csv file into the temp.fiscal_master table:

mysql> load data local infile '/path/to/fiscal_master.csv' into table temp.fiscal_master fields terminated by ',' ignore 1 lines;

**fiscal_year**

> use dashboard;

> truncate table fiscal_year;

> insert fiscal_year(fiscal_year_id, name, first_day_of_fiscal_year)

select distinct

fiscal_year_id,

year_name,

first_day_of_fiscal_year

from temp.fiscal_master

order by fiscal_year_id asc;

update fiscal_year y

set last_day_of_fiscal_year = (

select max(m.last_day_of_period)

from temp.fiscal_master m

where m.fiscal_year_id=y.fiscal_year_id

);

**fiscal_quarter**

> truncate table fiscal_quarter;

> insert fiscal_quarter(fiscal_quarter_id, fiscal_year_id, quarter_number, name, first_day_of_quarter)

select distinct

fiscal_quarter_id,

fiscal_year_id,

quarter_number,

quarter_name,

first_day_of_quarter

from temp.fiscal_master

order by fiscal_quarter_id asc;

> set @Row_ID=0;

update fiscal_quarter

set fiscal_quarter_seq = (

select @Row_ID := @Row_ID + 1

);

> update fiscal_quarter q

set last_day_of_quarter = (

select max(m.last_day_of_period)

from temp.fiscal_master m

where m.fiscal_quarter_id=q.fiscal_quarter_id

);

**fiscal_month**

> truncate table fiscal_month;

> insert fiscal_month(fiscal_month_id, name, fiscal_year_id, period_number, fiscal_quarter_id, first_day_of_period, last_day_of_period)

select distinct

fiscal_month_id,

period_name,

fiscal_year_id,

period_number,

fiscal_quarter_id,

first_day_of_period,

last_day_of_period

from temp.fiscal_master

order by fiscal_month_id asc;

> set @Row_ID=0;

update fiscal_month

set fiscal_month_seq = (

select @Row_ID := @Row_ID + 1

);

> update fiscal_month mon

set duration_days = timestampdiff(day, first_day_of_period, last_day_of_period) + 1;

> update fiscal_month

set

last_year_fiscal_month_id = fiscal_month_id - 100,

last_month_fiscal_month_id = fiscal_month_id - 1;

> update fiscal_month

set last_month_fiscal_month_id = last_month_fiscal_month_id - 100 + 12

where last_month_fiscal_month_id/100 - floor(last_month_fiscal_month_id/100) = 0;

**calendar_day**

> update calendar_day set fiscal_month_id = null;

> update calendar_day set fiscal_quarter_id = null;

> update calendar_day set fiscal_year_id = null;

> update calendar_day d

set d.fiscal_month_id = (

select m.fiscal_month_id

from fiscal_month m

where d.calendar_date between m.first_day_of_period and m.last_day_of_period

);

> update calendar_day d

join fiscal_month m

on d.fiscal_month_id = m.fiscal_month_id

set

d.fiscal_quarter_id = m.fiscal_quarter_id,

d.fiscal_year_id = m.fiscal_year_id;

You've now updated your fiscal caledar in Metric Insights!