Date Proximity Row Highlighting in Sisense Pivot Table Widgets [Linux]
Introduction
This article demonstrates how to automatically highlight rows in a Sisense Pivot Table widget based on how close a date column is to today's date. The script developed here adds visual urgency to your pivots by colour-coding each row based on the number of days remaining until the date in a specified column:
Light green for rows where the deadline has already passed (expired).
Light red for rows where the deadline is critically close (urgent).
Light yellow for rows where the deadline is approaching within a configurable warning window.
No highlight for rows where the deadline is comfortably in the future.
This is particularly useful for dashboards tracking contract renewals, project deadlines, task due dates, or any time-sensitive data where users need to spot at-risk rows at a glance.
The solution is tested on Sisense L2026.1.2 and works for both cloud and on-premises deployments.
Step-by-Step Guide
1. Prepare your environment
Open an existing dashboard that contains a Pivot Table widget, or create a new one. Make sure your table includes at least one column that contains date values. The script works best with dates formatted as mm/dd/yyyy or yyyy-mm-dd, which are the standard Sisense table output formats. You can use attached .dash and .sdata for testing.
2. Identify your date column index
The script uses a 0-based column index to locate the date column. This means the first column is index 0, the second is 1, the third is 2, and so on.
Count the columns in your table from left to right and note the position of your date column. You will use this number in the configuration section of the script.
3. Open the widget script editor
Click the three-dot menu on the Pivot Table widget → Edit Script.
4. Paste the full script
/**
* Date Proximity Row Highlighting
*
* Compares a specific date column against today's date and paints the row
* yellow (if the deadline is approaching) or red (if past due).
*/
// --- CONFIGURATION ---
const DATE_COLUMN_INDEX = 2; // 0-based index of your date column (e.g., 2 is the 3rd column)
const DAYS_WARNING = 14; // Highlight yellow if the deadline is within this many days
const DAYS_URGENT = 5; // Highlight red if the deadline is urgent (within this many days)
const EXPIRED_COLOR = '#d4edda'; // Light Green for past-due/expired deadlines
const URGENT_COLOR = '#f8d7da'; // Light Red for urgent deadlines
const WARNING_COLOR = '#fff3cd'; // Light Yellow for approaching deadlines
widget.on('domready', function () {
// Scope our DOM search to this specific widget only
const $widget = $(element);
// Find all data rows in the table body
$widget.find('table tbody tr').each(function () {
const $row = $(this);
const $cells = $row.find('td');
// Safety check: ensure the row actually has our date column
if ($cells.length <= DATE_COLUMN_INDEX) return;
// Extract the text of the date column
const dateText = $cells.eq(DATE_COLUMN_INDEX).text().trim();
// Skip empty rows or standard 'Grand Total' style texts
if (!dateText || dateText.toLowerCase().includes('total')) return;
// Parse the date (Works best with mm/dd/yyyy or yyyy-mm-dd Sisense formats)
const rowDate = new Date(dateText);
// Only proceed if it is a valid date
if (!isNaN(rowDate.getTime())) {
const today = new Date();
// Strip the time to compare pure calendar dates
today.setHours(0, 0, 0, 0);
rowDate.setHours(0, 0, 0, 0);
// Calculate the difference in milliseconds and convert to days
const diffTimeMilli = rowDate.getTime() - today.getTime();
const diffDays = Math.ceil(diffTimeMilli / (1000 * 60 * 60 * 24));
// Apply color to all cells in the row to guarantee the background isn't obscured
if (diffDays < 0) {
// Deadline is in the past! (Expired) -> Light Green
$cells.css('background-color', EXPIRED_COLOR);
}
else if (diffDays <= DAYS_URGENT) {
// Deadline is extremely close! (Urgent) -> Light Red
$cells.css('background-color', URGENT_COLOR);
}
else if (diffDays <= DAYS_WARNING) {
// Deadline is approaching within our warning threshold! -> Light Yellow
$cells.css('background-color', WARNING_COLOR);
}
else {
// Clear out the color if it's safe
$cells.css('background-color', '');
}
}
});
});
5. Configure the script constants
At the top of the script, update the configuration constants to match your table and your business rules:
const DATE_COLUMN_INDEX = 2; // 0-based index of your date column (e.g., 2 = 3rd column)
const DAYS_WARNING = 14; // Highlight yellow if deadline is within this many days
const DAYS_URGENT = 5; // Highlight red if deadline is within this many days
const EXPIRED_COLOR = '#d4edda'; // Light Green for past-due/expired rows
const URGENT_COLOR = '#f8d7da'; // Light Red for urgent rows
const WARNING_COLOR = '#fff3cd'; // Light Yellow for approaching rowsDATE_COLUMN_INDEX – Set this to the 0-based position of your date column.
DAYS_WARNING – Any row whose date is within this many days from today will be highlighted yellow. The default is 14 days.
DAYS_URGENT – Any row whose date is within this many days from today will be highlighted red. The default is 5 days. This threshold takes priority over the warning threshold.
Colour constants – The default colours follow a standard traffic-light convention. You can replace any hex value with your organisation's brand colours.
6. Click Apply and test
Save the script and reload the widget. Rows containing dates will now be colour-coded automatically based on their proximity to today. Rows with dates safely in the future will have no background colour change.
Important notes
Date format compatibility: The script relies on JavaScript's native new Date() parser. This works reliably with mm/dd/yyyy and yyyy-mm-dd formats. If your Sisense instance is configured to display dates in a different format (e.g. dd/mm/yyyy), the parser may misinterpret the values. In that case, you will need to add a custom date parsing step before the new Date(dateText) call.
Pagination: The script runs on domready, which fires each time the table re-renders – including after a page change. This means the highlighting is automatically reapplied when the user navigates to a different page of results.
Grand Total rows: The script automatically skips any row whose date cell contains the word "total", preventing the Grand Total row from being incorrectly highlighted.
Conclusion
By hooking into the domready event and reading the rendered cell values directly from the DOM, this script adds a real-time, date-aware traffic-light system to any Sisense Pivot Table widget – with no changes required to the underlying data model. The configuration constants at the top of the script make it straightforward to adapt the column position, day thresholds, and colours to any use case. The same pattern can be extended to highlight rows based on other conditions, such as numeric thresholds, status text values, or combinations of multiple columns and conditions.
References / Related content
Disclaimer:This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.
