Download: Limit Filter
When creating tables of data, sometimes you want to limit the results based not for the entire widget, but for a top/bottom count that's unique per row.
An example would be if you want to show a table of all products with only their most recent transaction price. In this case, you can't set a date filter for the entire widget since the most recent transaction date is likely different for each product. Showing this in a regular pivot table would look like this
This plugin allows you to add a filter to look at only the Top/Bottom X members from a specific column, which can result in a table like this
Steps/Notes
The following steps will walk through the process of adding the new chart type and creating a sample funnel chart.
STEP 1 - ADD THE PLUGIN
Download the attachment and unzip the contents into your C:\Program Files\Sisense\PrismWeb\plugins\ folder. If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder. If the plugins folder doesn't exist, just create it. After those files have been unzipped there, you may also have to restart the web server.
STEP 2 - CREATE YOUR DASHBOARDS(S)
There are no extra configuration steps, this plugin is available as a popup menu from the widget editor. Just click on the settings menu of the field you want to limit, and click on the Limit Filter option. If you have a filter already added, this option will show a checkbox next to the label.
This opens a popup menu, where you can select Top/Bottom, a count, and specifying the field that you want to limit based on. If you need to remove the limit filter, just click on the trash can icon just above the green save icon.
Most of the time this field will be the same field you are trying to display, but this is configurable in case you want a different logic. So for example, if you don't want to show the Order Date within the above table but still want only the most recent purchase price, then you would limit the Price field based on the Top 1 Order Date.
References/Notes
There is an additional consideration required when using this plugin with fields coming from different tables. Lets say for example, you have a fact table with transactions and a dimension table that has a row for every day in the dataset. If you limit the date from the dimension table to the last 5 days, there are actually two queries that get run behind the scenes. First the max 5 days from the date dimension are fetched, and then the results are joined to the fact table. This means that if the last 5 days are Jan 1 - Jan 6 but there were only transactions on Jan 3rd, the table visible on the dashboard will only show Jan 3rd.
This plugin works by adjusting the JAQL query on the fly to add/remove filters from specific items. If you observe the JAQL Query normally made for the first pivot table screenshot, you can see what gets changed by this plugin. Instead of using a standard dimension reference in the query, this is adjusted to include a filter by rank.
Original JAQL
{
"datasource": {
"fullname": "LocalHost/Adventure Works"
},
"format": "pivot",
"count": 15,
"offset": 0,
"grandTotals": {},
"metadata": [{
"jaql": {
"table": "Product Category",
"column": "Category",
"dim": "[Product Category.Category]",
"datatype": "text",
"merged": true,
"title": "Category"
},
"field": {
"id": "[Product Category.Category]",
"index": 0
},
"panel": "rows"
}, {
"jaql": {
"table": "Product Subcategory",
"column": "Subcategory",
"dim": "[Product Subcategory.Subcategory]",
"datatype": "text",
"merged": true,
"title": "Subcategory"
},
"field": {
"id": "[Product Subcategory.Subcategory]",
"index": 1
},
"panel": "rows"
}, {
"jaql": {
"table": "Product",
"column": "Name",
"dim": "[Product.Name]",
"datatype": "text",
"merged": true,
"title": "Product"
},
"field": {
"id": "[Product.Name]",
"index": 2
},
"panel": "rows"
}, {
"jaql": {
"table": "Order Headers",
"column": "OrderDate",
"dim": "[Sales.OrderDate1 (Calendar)]",
"datatype": "datetime",
"merged": true,
"level": "days",
"title": "Last Order Date"
},
"field": {
"id": "[Sales.OrderDate1 (Calendar)]_days",
"index": 3
},
"panel": "rows"
}, {
"jaql": {
"table": "Order Details",
"column": "UnitPrice",
"dim": "[Order.UnitPrice1]",
"datatype": "numeric",
"title": "Max UnitPrice",
"agg": "max"
},
"field": {
"id": "[Order.UnitPrice1]_max",
"index": 4
},
"panel": "measures"
}],
"isMaskedResult": true
}Modified JAQL
{
"datasource": {
"fullname": "LocalHost/Adventure Works"
},
"format": "pivot",
"count": 15,
"offset": 0,
"grandTotals": {},
"metadata": [{
"jaql": {
"table": "Product Category",
"column": "Category",
"dim": "[Product Category.Category]",
"datatype": "text",
"merged": true,
"title": "Category"
},
"field": {
"id": "[Product Category.Category]",
"index": 0
},
"panel": "rows"
}, {
"jaql": {
"table": "Product Subcategory",
"column": "Subcategory",
"dim": "[Product Subcategory.Subcategory]",
"datatype": "text",
"merged": true,
"title": "Subcategory"
},
"field": {
"id": "[Product Subcategory.Subcategory]",
"index": 1
},
"panel": "rows"
}, {
"jaql": {
"table": "Product",
"column": "Name",
"dim": "[Product.Name]",
"datatype": "text",
"merged": true,
"title": "Product"
},
"field": {
"id": "[Product.Name]",
"index": 2
},
"panel": "rows"
}, {
"jaql": {
"table": "Order Headers",
"column": "OrderDate",
"dim": "[Sales.OrderDate1 (Calendar)]",
"datatype": "datetime",
"merged": true,
"level": "days",
"title": "Last Order Date",
"filter": {
"top": 1,
"by": {
"table": "Order Headers",
"column": "OrderDate",
"dim": "[Sales.OrderDate1 (Calendar)]",
"datatype": "datetime",
"merged": true,
"level": "days",
"agg": "max"
}
}
},
"hierarchies": ["calendar", "calendar - weeks"],
"field": {
"id": "[Sales.OrderDate1 (Calendar)]_days",
"index": 3
},
"panel": "rows"
}, {
"jaql": {
"table": "Order Details",
"column": "UnitPrice",
"dim": "[Order.UnitPrice1]",
"datatype": "numeric",
"title": "Max UnitPrice",
"agg": "max"
},
"field": {
"id": "[Order.UnitPrice1]_max",
"index": 4
},
"panel": "measures"
}],
"isMaskedResult": true
}