Dashboard Filters

Hi Harish,

To customize the quarter definitions in your Sisense dashboard to align with a financial year where Q1 starts in April, you will need to create a custom field or use a script to adjust the quarter calculations. Sisense typically uses calendar quarters by default, so customizing this requires a bit of manipulation.

Here’s a solution that involves creating a custom calculated field that redefines the quarters based on your financial year:

Step 1: Add a Custom Calculated Field

1. Open the Elasticube Manager: Start by opening the Elasticube Manager and navigating to the table that contains your date field.
2. Create a Calculated Field: Add a new calculated field to your table. This field will contain the logic to assign dates to the correct financial quarters.

Step 2: Define the Financial Quarters Logic

In the calculated field, you'll write a case statement to define each quarter of the financial year. Assuming your date field is named DateField, the logic would look something like this in SQL:

CASE
    WHEN MONTH(DateField) BETWEEN 4 AND 6 THEN 'Q1'
    WHEN MONTH(DateField) BETWEEN 7 AND 9 THEN 'Q2'
    WHEN MONTH(DateField) BETWEEN 10 AND 12 THEN 'Q3'
    WHEN MONTH(DateField) IN (1, 2, 3) THEN 'Q4'
    ELSE 'Unknown'
END AS FinancialQuarter

This SQL statement categorizes each date into the correct financial quarter based on the month the date falls into.

Step 3: Build the ElastiCube

After adding and saving the calculated field, you need to build the ElastiCube to apply the changes and make the new financial quarter field available for use in your dashboards.

Step 4: Use the Custom Field in Your Dashboard

1. Open the Dashboard: Open the dashboard where you want to apply the custom financial quarters.
2. Add/Modify the Filter: Add a new filter or modify an existing one to use the FinancialQuarter field you created instead of the standard time field.
3. Configure Your Table: In your table widget, use the FinancialQuarter field to group or filter your data at the quarter level according to your financial year.

Additional Tips

• Displaying Year with Quarter: You might also want to display which year each quarter belongs to. You can achieve this by creating another calculated field that concatenates the year with the financial quarter, like CONCAT(YEAR(DateField), '-', FinancialQuarter).
• Advanced Calculations: For more advanced scenarios, you might need to use scripting within the Sisense dashboard or leverage JavaScript to manipulate data further on the client side.

This approach lets you tailor the quarter definitions to fit your financial year, ensuring that your dashboard aligns with your organizational reporting requirements.

Derek

RAPID BI

[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons