Managing Customers with Varying Fiscal Year Start Dates

[intapiuser]

 Analytical Need 

If a dataset contains customer data for various customers and we want to compare quarterly data across or within customers, we can use the time functions within Sisense. If a fiscal calendar is the same for the entire set of customers, we can change the fiscal year start date for the entire environment in our settings, but that isn’t enough for this use case. Our customers have varying fiscal year start dates.

To compare one customer's Q1 to another's Q1, we need to make changes to the data model. We know the date the customers consider to be the fiscal year start, but Q1 for Customer 1 be Q4 for Customer 2.

I am using the attached .ecdata and .dash files for this solution (text of custom fields is here). See this article on importing a .dash file, and this on importing an .ecdata file. 

 Modeling Challenge

Not all my customers have the same fiscal year start date. I want to filter and compare quarterly revenue by their own fiscal year calendars.

How can I create a way to identify the correct fiscal quarter for each customer when two dates might represent different quarters for different customers?

Solution

Data Requirements

• The fact table has a reference to which customer is linked to each record
• For each customer, we have a reference to the month and day of the fiscal year start (having a year is also fine, as with this example).

  

First, we need to identify how many days each fiscal year is offset from the default start date of January 1st. We will do this with the creatdate(), daydiff(), and lookup() functions. We use the lookup function to get the fiscal year start date for each customer and then createdate() and datediff() to get the amount of days after January 1^st. We use 2012 because it’s a leap year so we avoid running into an error if we create a date that doesn’t exist.

After running a schema change build, we use the new field to convert the customer’s date to the traditional fiscal year date version of itself using adddays().

 

Now when we want to identify the fiscal year for each customer based on fiscal quarter, day, etc., we can use our new field! We have both the actual and fiscal equivalent date available for analysis.