Comparative analysis using the filtered measure plugin
Comparative analysis using the filtered measure plugin By default, filter selections in Sisense are applied at the dashboard or widget level. The Filtered Measure add-on enables the creation of additional selection states, allowing you to display two or more different sets of selections within a single widget. For more details, please refer to the link below. Here are some common use cases for Filtered Measures: Comparative Analysis Example: Compare the same metric (e.g., Revenue) across two different date ranges (e.g., this month vs. last month) to analyze trends or identify growth patterns. Filter Applied Directly on a Measure Instead of applying a filter to the entire widget or dashboard, apply the filter directly to a specific measure (e.g., applying a filter to "Sales" based on a particular product category, without affecting other parts of the dashboard). This allows for more granular control over the displayed data while preserving other elements of the analysis. This article highlights a comparative analysis of a single measure, Revenue, across multiple dimensions—Date and Category. It incorporates two filters for each dimension: Primary and Compare. Background When it comes to comparative analysis, dashboards are often designed to display measures broken down by the dimensions being compared. This approach can make it challenging to extract specific values and may require adding additional dimensions for grouping. Example Dashboard Alt text: A dashboard displaying total revenue information. At the top, there is a summary showing total revenue of $263,661, with a breakdown indicating that 61% comes from Europe and 39% from the USA. Below, bar graphs illustrate total revenue by category, listing categories such as Bikes, Body Armor, Cables & Hubs, and more, with respective revenue figures. At the bottom, a line graph represents total revenue over several months, showing fluctuations with values ranging from $1.6K to $29.3K. This dashboard currently does not address the following questions: How can we filter by Category and compare Bikes to Wheels & Wheelsets across different Regions? How can we evaluate the performance of the Category Bikes against Wheels & Wheelsets over time? How can we compare a specific group of members within a Category to the overall total of the Category? How can we compare specific date ranges, such as Yesterday versus the same day last week? How can we provide flexible date selection options tailored to the user’s preferences? Filtered Measure - Implementation Steps Enable the Measured Filter Plugin Ensure the Measured Filter Plugin is activated in your environment. Modify the Data Model - Navigate to the Data Model. - Locate the Dimension table that requires a comparison - Duplicate the column you want to compare (e.g., the Date column) and assign it a meaningful name, such as Date_compare - Build or Publish the data model after applying the changes Prepare the Dashboard - Create a new dashboard or update an existing one. - Add both filters - Primary and Compare - as Dashboard Filters - Maintain a clear naming convention for the filters (e.g., Date for the primary filter and Date_compare for the comparison filter). Update Widgets with Measure Filters - Create a new widget or update an existing one - Add the relevant measure filters (Primary and Compare) to the metric. For example: ALT text: "Screenshot of a formula editor displaying a formula that sums order revenue based on category name and years in date." Rename Filter Measures - Rename the filter measures by adding an @ symbol before the name. - Update the formula syntax to include the renamed filters. Example ALT Text: A screenshot of a formula editor displaying a calculation formula in a spreadsheet application. The formula is a SUM function that includes parameters for "Order Revenue," "Category Name," and "Date." The text is shown in a code-like format, with certain elements in blue. - Ensure that the filter name in the dashboard matches the Measure Filter name used in the formula. - Repeat the Same for the Compared measure ALT text: A screen displaying a formula editor in a data visualization tool. The formula shows a calculation using the SUM function on "OrderRevenue," referencing "CategoryName_Compare" and "Date_Compare." The interface includes sections labeled "Values" and "Break by." Key Points of the Filtered Measure Plugin The @ symbol is required in the formula to apply the dashboard filter as a parameter. However, it should not be added to the name of the dashboard filter itself. Grouping by the Compared Dimensions is unnecessary and adds no value. Filtered measures support multiple measures and dimensions In the same formula, it is possible to include measures for both the Primary measure and the Compare measure. For example: ALT Text: A screenshot displaying a data analysis tool. The left side shows a panel for selecting categories labeled "Region" and "Values," while the lower section features a formula editor with a mathematical expression for calculating revenue compare by region. For Indicator, Blox widgets, and any widget using a single measure applied to either Primary or Compare, disable the corresponding filter. Similarly, turn off the other filter when switching between them. ALT Text: A screenshot of a data visualization interface showing "Revenue Primary" as the main heading. Below it, there are options labeled "Date Compare" and "Category Level Compare" with a highlighted checkbox next to each option. The current displayed revenue amount is $95,751, prominently positioned in the center of the screen. The interface includes buttons labeled "Apply" and "Cancel" at the top. If both measures, Primary and Compare, are used, ensure that both filters are enabled. Here are some common examples of comparative analysis: Use Case 1 - Compare Category Dimension ALT Text: A dashboard displaying revenue statistics. At the top, two sections show “Revenue Primary” and “Revenue Compare,” both with a figure of $76,274. Below, a third section displays “Revenue Compare Over Time” with a line graph illustrating revenue trends, peaking around $62,597. The bottom section features a table listing revenue by employee, with names and corresponding revenue figures, including entries for Andrew Williams and Emily Johnson. The visualizations include bar charts, line graphs, and a detailed employee revenue table. This example compares between Category Bikes and Wheels & Wheelsets over 3 groups by dimensions: Displaying the Revenue asan Indicator for each one of the Categories. Compare the Revenue over time ALT Text: A line graph titled "Revenue Compare Over Time" showing two data series. The blue line represents "Revenue," while the orange line indicates "Revenue Compare." The vertical axis measures revenue in dollars, ranging from $0 to $30,000. The horizontal axis displays dates from May 2011 to March 2012. The graph illustrates fluctuations in both revenue metrics over the specified time period with markers indicating values at various points. Compare the Revenue by Region Summary Table of Revenue Comparison by Employee Use Case 2 - Compare multiple Dimensions: Date and Category ALT Text: A data visualization dashboard displaying revenue metrics. The top section shows total revenues for "Revenue Primary" ($33,832) and "Revenue Compare" ($34,743). Below, a bar chart compares revenue by region, with separate bars for Europe and USA. Another chart shows revenue comparison by employee, with individual bars for names including Aria Williams, John Doe, Emily Johnson, and others. The dashboard features filters on the right side for data customization. This example compares Dates and Categories. The implementation of the measured filter is the same as shared above: (sum([Order Revenue]),[@CategoryName],[@Date]) Enable dynamic comparison between different time ranges from the viewer's side, rather than being predefined by the dashboard designer. Compare Category and Date over time Use Case 3 - Compare Different Date Ranges with Different granularities Compare Year to Single Quarter Example ALT Text: A data visualization showing revenue comparisons across different territories and employees. The top section displays a horizontal bar chart comparing revenue by TerritoryID, with revenue values in blue bars and revenue compare values in orange. The lower section features a bar chart comparing revenue by employee, with blue bars representing individual revenues and orange bars indicating revenue comparisons. Key figures are labeled, with total revenue values ranging from $380 to $16K. Date selection options are visible in the upper right corner, indicating the year 2012 and the fourth quarter of 2011. Compare Different Custom Date Ranges Example ALT Text: A data visualization dashboard displaying revenue comparisons. The upper section shows a horizontal bar chart labeled "Revenue Compare By TerritoryID" with blue bars indicating revenue amounts and orange bars representing revenue comparisons for various Territory IDs. The lower section features a vertical bar chart titled "Revenue Compare By Employee," highlighting revenue data for individual employees. Each bar is labeled with revenue amounts, with blue bars indicating revenue and a few orange bars for comparison. The sidebar includes date-selectors for filtering the data. Use Case 4 - Compare the selected Category with the total, excluding the selected category. This allows users to view the performance of a specific category in contrast to the remaining categories ALT Text: A dashboard displaying financial data, including three summary boxes at the top showing total revenue figures of $76,274 for Primary and $187,388 for Revenue Compare. Below, a graph illustrates revenue comparison over time with two lines plotting values for each month. To the left, there is a bar chart comparing revenue by region, highlighting Europe at $130K and USA at $74K. On the right, a table lists employee revenue with columns for employee names, revenue amounts, quantities, and total sales orders. There are filter options on the right side of the image. Best Regards Assaf
Limiting Date Range Filters in Sisense Dashboards
Wide date ranges in Sisense dashboards can lead to performance issues, especially when using live models or querying large datasets. For live data models, large queries increase costs as more data is pulled from the data warehouse. For Elasticubes, this can cause performance bottlenecks. To avoid these issues, here is a quick solution to "limit" the date range users can select, ensuring both cost-efficiency and smooth performance. Read more to find out how!Financial Formatting for Pivot Tables on Linux
Question How to apply Financial Formatting to Pivot Tables? Answer Financial reports usually have specific styling requirements where negative numbers are shown in parentheses as opposed to having a leading minus sign. They are also usually written in red. The script below formats all the negative numbers in pivot tables exactly as the requirements above. Feel free to modify it if you have different formatting requirements. // Format negative numbers widget.transformPivot({ type: ['value'] }, function(metadata, cell) { if(cell.value >= 0 || !cell.value) { cell.content = cell.value; cell.contentType = 'html'; } else { cell.content ='(' + (-1*cell.value).toString() + ')'; cell.style.color = 'red'; cell.contentType = 'html'; } });
How to Calculate YTD by Month in a pivot table?
Question How to Calculate YTD by Month in a Pivot Table? Answer If you want to show YTD by Month next to your monthly amount you can utilize the "Running Sum" Quick Calculation. If you want to show this next to the monthly amount, just bring in that same value again. If you just want to show total for the time period you can use the "Grand Total" on columns Custom function: =RPSUM([Total BUDGET],12) This formula takes the monthly budget for the year (as filtered by on my dashboard filter) and cumulatively adds it to the previous month's budget and cumulates from there. =RPSUM([Total BUDGET]) was working, but only for 2 months at a time (Jan + Feb, reset for March + April, etc). By adding the 12 it knows to repeat after 12 periods, so it takes care of the entire year. Apply the RPSUM with 12 periods to my Budget, Revenue, and Variance (Revenue - Budget) columns.
Pivot 2.0 - Replace the "-" Sign of a Negative Value by Parentheses
In Accounting, it is common to represent negative numbers with leading and trailing parentheses. For example, negative two hundred is displayed as "(200)" To change a negative number formatting in a pivot 2.0 chart - Add the following script to the pivot widget: function getWidgetDocumentObject(widget) { if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]').querySelector('pivot2'); else return document.querySelector('pivot2'); } widget.on('domready', function(widget) { try { console.log('DOM Ready script starting (wid=' + widget.oid + ')'); doc = getWidgetDocumentObject(widget); cells = doc.querySelectorAll('.table-grid__cell'); negative_cells = Array.from(cells).filter(cell => !isNaN(parseInt(cell.innerText.replaceAll(',',''))) && parseInt(cell.innerText.replaceAll(',',''))<0) negative_cells.forEach(function(element) { element.lastChild.innerText = element.lastChild.innerText.replace('-','(') + ')'; }); } catch (error) { debugger; console.error(error); } finally { console.log('DOM Ready script complete (wid=' + widget.oid + ')'); } }); Before After
