Advanced Scripting for Sisense’s Simply Ask NLQ: Adding Currency Symbols to Pivot Tables

[JeremyFriedel]

Advanced Scripting for Sisense’s Simply Ask NLQ: Adding Currency Symbols to Pivot Tables

Sisense’s Simply Ask feature empowers users to create visualizations instantly by asking natural language questions, simplifying data exploration without needing technical expertise or manual widget configuration. This Natural Language Query (NLQ) capability accelerates insights generation without the need to manually create new Sisense widgets through the standard UI for each question or data relationship of interest.

User interface of the 'Simply Ask - Sample ECommerce' dashboard in the Sisense data analytics tool. A central search bar at the top allows users to 'Ask a question about your data.' Below the search bar is a large icon with a plus symbol, inviting users to ask questions about dashboard data. On the right, there is a list of 'Available fields' categorized under Commerce, Category, Country, and Formulas, with individual fields like Brand ID, Amount, Age Range, and Revenue. A 'Done' button is highlighted at the bottom.

A previously published article discussed how to customize Simply Ask NLQ-generated widgets using scripting, providing multiple examples of basic modifications and more detail on the basic mechanics of scripting Simply Ask widgets. This article will share a more complex example to show how NLQ widgets can be customized in more complex conditional ways, in this example: programmatically adding currency symbols to specific data columns in pivot table widgets generated by Simply Ask.

Enhancing NLQ Widgets with Currency Symbols

When dealing with financial data, it’s can be important to display currency symbols alongside numeric values for clarity, especially if multiple currency may be used in a datasource. However, Simply Ask NLQ-generated widgets may not automatically include these symbols, especially if they are dynamically generated based on natural language user queries.

The following script demonstrates how to:

• Detect when a new NLQ widget is rendered.
• Identify specific data columns based on their titles using purely HTML based Javascript observation.
• Append a currency symbol to the numeric text values in those columns.

Full Script:

const addCurrencySignToValues = () => {
    // Column keywords to match (case-insensitive)
    const currencySignColumns = ['Revenue', 'Cost']; // Replace with actual column keywords as needed

    // Shared constants for selectors and class patterns
    const NLQ_MAIN_CONTENT_SELECTOR = 'div.nlq-widget__main-content';
    const DATA_CELL_SELECTOR = 'td.table-grid__cell--data';
    const HEADER_CELL_SELECTOR = 'td.table-grid__cell--row-0';
    const INNER_CONTENT_SELECTOR = 'div.table-grid__content__inner';
    const ROW_CLASS_PREFIX = 'table-grid__cell--row-';
    const COLUMN_CLASS_PREFIX = 'table-grid__cell--col-';
    const NUMBER_PATTERN = /^[0-9,.]*$/;

    // Determine the default symbol to use
    let symbol = '$';

    // Function to get the cell's row and column indices
    const getCellPosition = (cell) => {
        let classList = cell.classList;

        // Find row and column classes
        let rowClass = Array.from(classList).find(cls => cls.startsWith(ROW_CLASS_PREFIX));
        let columnClass = Array.from(classList).find(cls => cls.startsWith(COLUMN_CLASS_PREFIX));

        // If both classes are found, extract indices
        if (rowClass && columnClass) {
            let rowIndex = rowClass.split(ROW_CLASS_PREFIX)[1];
            let columnIndex = columnClass.split(COLUMN_CLASS_PREFIX)[1];
            return {
                row: parseInt(rowIndex, 10),
                column: parseInt(columnIndex, 10)
            };
        }

        // Return null if not found
        return null;
    };

    // Build a mapping of column indices to titles
    let columnHeaders = {};

    // Collect header cells (row 0)
    $(HEADER_CELL_SELECTOR).each(function () {
        let cellPosition = getCellPosition(this);
        if (cellPosition) {
            let headerText = $(this).text().trim();
            columnHeaders[cellPosition.column] = headerText;
        }
    });

    // Process data cells
    $(`${NLQ_MAIN_CONTENT_SELECTOR} ${DATA_CELL_SELECTOR}`).each(function () {
        let cellText = $(this).text();
        let cellPosition = getCellPosition(this);

        if (cellPosition) {
            let columnIndex = cellPosition.column;
            let columnTitle = columnHeaders[columnIndex];

            // Check if column title includes any of the keywords (case-insensitive)
            if (
                currencySignColumns.some(keyword =>
                    columnTitle.toLowerCase().includes(keyword.toLowerCase())
                ) && NUMBER_PATTERN.test(cellText)
            ) {
                let changedText = `${cellText}${symbol}`;
                // Modify the text within the inner div
                $(this).find(INNER_CONTENT_SELECTOR).text(changedText);
            }
        }
    });
};

// Set up the MutationObserver to detect when new NLQ widgets are rendered
dashboard.on('domready', function () {
    $('button.nlq-main-button').click(function () {
        setTimeout(function () {
            // Observe changes in the widget container
            const elementToObserve = $('div.nlq-widget__main-content')[0];
            const observer = new MutationObserver(function () {
                addCurrencySignToValues();
            });

            observer.observe(elementToObserve, { subtree: true, childList: true });
        }, 1000);
    });
});
​

How It Works

• Define Column Keywords: The currencySignColumns array variable contains the keywords for column titles to target (in this example, ‘Revenue’ and ‘Cost’). The script will search for these keywords in the pivot column headers to determine which columns should have the currency symbol appended.
• Set Up Constants for CSS Class Names: Constants for selectors and class prefixes are defined for easy maintenance and readability.
• Determine the Currency Symbol: By default, in this example the symbol is set to '$'. It can be modified to any symbol required. If needed this can be be read from a Javascript variable set by a plugin or other script, to determine the correct currency symbol to use for a dashboard and datasource.
• Get Cell Position Function: The getCellPosition function extracts the row and column indices from a cell’s class list, which follows a naming convention contained in class names.
• Build a Column Headers Map: The script builds a mapping (columnHeaders) of column indices to their respective header titles by iterating over header cells. This same concept and code can be used in other scripts that customize pivots based on header titles.
• Process Data Cells: Iterates over all data cells, checks if cell's column title matches any of the keywords, and if the cell content is numeric.
• Append Currency Symbol: If both conditions are met, the script appends the currency symbol to the cell’s text within the inner content div.
• Set Up Mutation Observer: The script uses a MutationObserver to detect when a new NLQ widget is rendered in the Simply Ask modal. It observes the main widget content element for changes and triggers addCurrencySignToValues when mutations occur.

Customization Tips

• Updating Keywords: Modify the currencySignColumns array to include any keywords relevant to the data columns in question.
• Changing the Currency Symbol: Change the symbol variable to use a different currency symbol or to dynamically set it based on user preferences or locale.
• Adjusting Number Pattern: The NUMBER_PATTERN regex can be adjusted to match different numeric data includes different formats (e.g., decimal points, thousand separators).
• Use as Template: This example can be used as a template for completely different types of custom scripting of Simply Ask NLQ widgets.

Integrating the Script into a Dashboard

To use this script in a Sisense environment:

• Add the Script to the Dashboard: Place the script within the dashboard’s JavaScript editor. This will ensure it runs when the dashboard is loaded.

Considerations

• CSS Class Updates: CSS class names can change between Sisense versions; keeping CSS selector strings as variables allows for easy updating if needed.

Conclusion

By leveraging standard JavaScript, and dashboard scripting NLQ-generated widgets can be enhanced in Sisense’s Simply Ask feature with advanced customizations like appending currency symbols to specific data columns. This approach allows for maintaining consistency and clarity in data presentations, providing a better experience for users interacting with dynamically generated visualizations. This example shows that complex widget scripting customizations are possible with Simply Ask NLQ generated widgets.

For more details on scripting with Simply Ask NLQ and additional examples, refer to the previous article Modifying Simply Ask Natural Language Query Generated Widgets with Scripting.