cancel
Showing results for 
Search instead for 
Did you mean: 

Sort and Filter multiple custom Values (formulas)

Jake_Raz
10 - ETL
10 - ETL

I want to create a bar chart. Under Categories would be "Common Term", which is dropdown field that has close to 300 distinct options a user can select. The size of each bar would be the number of Matters that contain each Common Term. The problem is that there's too many Common Terms, and my leadership has requested if there's a way to group similar terms. I came up with around ~25 groupings that we could 'bucket' the individual Common Terms into. Basically, instead of having 300 bars, we'd go down to just 25 bars. The matter counts for each should still total everything up appropriately (e.g. if Common Term A was 15 and Common Term B also was 15, then the combined grouping for those two would show 30).

I've already figured out how to use Measured Values to accomplish this in the UI. Each grouping is a formula in the Values section of the widget that uses something like this:

 

(count([Matters]),[Common Term])

 

Whereupon I'd click on the [Common Term] item, select "Edit Filter", and then select the individual Common Terms I want to be counted for this particular grouping. Since I had 25 particular groups I wanted to create, I now have 25 items under the Values section of the widget.

Jake_Raz_0-1707424393917.png

This seemed to accomplish what I want. However, there's a few downsides to this method. First off, it seems like measured values always show in the view, regardless of their actual total (even if they're zero). Second, there's no way to automatically sort the measured values based on their totals. Third, there's no way to filter the groupings by Top N (as in: out of the 25 groupings, only show me the Top 5 groupings by number of matters). Fourth, there's no way drill into a particular grouping to see its underlying members. Lastly, this method is labor intensive to set up, difficult to modify or maintain, and is not scalable (imagine trying to apply a similar method to a field with over a thousand distinct values, with hundreds of potential groupings).

At this point I realize the best solution would be to add a new column to the eCube for the groupings I need. Then this would just show as a new field in Sisense and I could simply put it in the "Category" section which would let me accomplish the various things I'm trying to do (sort, filter, etc). However, for various reasons, this is not a good option for me (i.e. modifying the eCube). We could potentially do this for this one specific situation (the Common Terms) but it would be difficult/tedious to maintain or make changes to the groupings. Plus, I'd like a solution that's more flexible and can be applied in a more ad-hoc or arbitrary manner, similar to how you can select multiple items in a pivot table in Excel and select "Group" to create temporary bespoke groupings (i.e. they only exist in that pivot table and not in the original data).

Is this possible at all, using vanilla Sisense? If not, is there perhaps a plugin we can install that would allow us to achieve this sort of grouping? Or do I just need to bite the bullet and accept that this is really only possible through modifying the eCube?

5 REPLIES 5

DRay
Community Team Leader
Community Team Leader

Hello @Jake_Raz,

I'm not able to comment on creating custom plugins, but I wonder if using Perspectives might be a way forward for you? That way you don't have to change your current Data Model, and could use Custom Columns to group the data.

Do you think that will work for you? If not, definitely let us know and we can look into it further.

David Raynor (DRay)

Hi @DRay! That link is interesting but I'm not sure if it'll work for our situation. We're on an older version of Sisense that doesn't have that feature. Even if we had the feature, it appears that it's only something that admins can modify in the backend area of Sisense, which I don't have access to anyway 😞

DRay
Community Team Leader
Community Team Leader

Hi @Jake_Raz,

Thank you for getting back to me. What version of Sisense are you using?

David Raynor (DRay)

Morning  I believe we are using v8.2.1, Windows (not Linux). I'm not sure how old it is. Technically we are using a product called TeamConnect Business Intelligence (TCBI), which is a reporting tool for our matter management software (TeamConnect), but it is basically just Sisense with a few minor tweaks.

I've been told that they plan on releasing a new version of TCBI soon, one that's based on a more recent Linux version of Sisense (2023.3), though I don't know exactly when that'll be or how soon my team will actually be able to update to it.

DRay
Community Team Leader
Community Team Leader

We have resources here at Sisense that can help you migrate to Linux. The Windows version will not be getting updated as frequently and will not be getting new features, just critical bug fixes. Please reach out to your Sisense account team for more information. 

David Raynor (DRay)