cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
 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 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.
Version history
Last update:
‎03-02-2023 08:54 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email community@sisense.com