If a dataset contains customer data 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. We have customers that have varying fiscal year start dates.
If customers have varying fiscal year start dates, 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.
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?
- 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 1st. 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.