Include All Dimension Values (Left Outer Join)

⬇️ Download using the attachment below ⬇️

Introduction

A common requirement when performing grouping and aggregate operations, is to include all dimension values in the group irrespective of the contextual filters applied on the transaction table.

e.g. For a given QTR show me a list of Customers and Total Sales - Including those Customers who have not made any purchases in the QTR.

Installation and Setup

Download the file from the link. UnZip it and place the OJ folder within the plugins folder of the file management UI.

Admin > System Management > File Management > plugins

You should now see the Include All Dimensions option in the widget properties menu for supported widgets.

Note: It is recommended that you refresh the dashboard after enabling the plugin on any widget.

Technical Details and Limitations

Works on Sisense Linux 8.2.4.x onwards. Sisense Windows Not Supported.
Works on Pivot / Column / Line Chart widget types only.
Works with widgets based on both Live and Elasticube data models.
Supports data security.
Not conducive for pivots with more than 50,000 rows returned.
Sort on measure option not supported.
Subtotal and Grand Totals supported for summation rollups only.
The plugin will respect any filter directly applied to the table of the Group field(s).

Note:

You may see the infamous yellow triangle after applying the plugin. - Refresh the dashboard to address this issue.
For pivots returning a large number of records, you may see blank results followed by a widget refresh. This is due to the asynchronous nature of JAQL queries executing under the hood.
If your widget has multi-level grouping with dimensions linked across a fact table. e.g. Customer > Orders > Sales Rep. Then the pivot will not display all the mathematical combinations but rather all combinations of Sales Rep and Customer in the Orders table.

intapiuser

Posted 3 years ago·Last reply 1 year ago

4 comments

Will

·1 year ago

Ho Martha , I stopped using it, and I don't remember exactly what I had done.

I'd suggest you make this change in blue below. It's in line 87 of the ojay.js file.

function isSupportedType(wType) {
    return ('pivot2|chart/line|chart/column|chart/bar'.indexOf(wType) > -1);
}

Martha

·1 year ago

Hi Will ,

I would like to use this plugin with bar charts - could you provide me with your changed plugin please?

Thanks, Martha

Will

·3 years ago

Hey intapiuser , thanks for the plugin; it helped my team a lot.

I did a small change to allow it to work on Bar Charts too. Don't you have a github repo for it?

Thanks

mismailansari

·1 year ago

Please provide the plugin with bar charts for left join filter.