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.
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?
