Aggregate Categorical Data to Simplify Analysis

[bminehart]

We would like to be able to dynamically aggregate different categories of data into a fewer number of categories to make the visualization and analysis of that data cleaner and more Viewer-friendly.

Example: I may have records of a color that I want to count on. I may have these possible values: "Red", "RED", "red", "Blue", "BLUE", "blue", "Orange", "ORANGE", "orange", and so on.

If I COUNT on these values I may get:

"Red": 10

"RED": 7

"red": 12

"Blue": 2

"BLUE": 7

"blue": 22

...and so on..

I'm asking for aggregation feature that I, as a user, could define based on the values in these records such taht the data above could be represented as:

"Red": 29 (i.e., 10 + 7 + 12)

"Blue": 31 (i.e., 2 + 7 + 22)

[Helena_qbeeq]

Hi @bminehart ,

You could achieve this kind of grouping on the data model side using augmented text deduplication.

Check documentation here:
https://docs.sisense.com/l2022_9/SisenseLinux/augmented-text-deduplication.htm

This would be done by the data modeler. Would this work for you, or would you be more interested in a solution that would be targeted for the viewer specifically?

Please let me know if this helps : )

Always here to help,
Helena from QBeeQ
helena.ferreira@qbeeq.io
QBeeQ - Gold Implementation and Development Partner

[bminehart]

Thanks Helena. We embed Sisense Dashboarding as a product to our customers and they do not have Data Model control so it wouldn't solve our problem.

We need something that is specifically exposed to users while Dashboarding.

[brandon_ni]

We are also confronted with a similar situation where our clients are unable to access the data model and, as a result, cannot rename the actual fields. In addition to this, there are instances when we need to rename or reclassify column values specifically for a certain widget or dashboard. 

[YuliyaMotiyets]

Hello @bminehart 

Thank you for sharing your idea in the Sisense Community. We value your input and think this is a great idea! 

In order to prioritize our development efforts, we want to gauge community interest. We kindly request you to encourage other users to vote and comment on your suggestion. The more engagement your request receives, the higher the likelihood it has of being considered for implementation.

We appreciate your efforts!

Best, 

Yuliya

[Jake_Raz]

I believe you can do this via formulas. You'd add a count to the Values, then modify the formula so it looks something like this:

(COUNT([thing you're counting]),[Category you want to group values by])

Then you'd click the category item, and in the pop-up you'd select "Edit Filter". Then you'd select only the particular values you want to group, e.g. "Red", "RED", "red", etc. When finished, the total being displayed should be the combination of all the 'Red' values.

Of course, the downside to this method is you need to repeat these steps for each grouping you want, so it's not very useful if you have a lot of them, or if kinds of groupings frequently change from widget-to-widget (or based on certain other filters). You can re-use certain complex groupings by saving the formula and naming it, which lets you re-use it elsewhere with the same configuration. However, another downside is that if a given formula will resolve to zero based on the other filters in the view, it will still try to show. That is: if you have ten formulas for the ten possible groupings you want, but because of your current filters only 3 of them are non-zero, the other 7 will still show in the view as empty spaces. Not ideal 😕

Another downside is that, because this is a formula, it only applies to "values". You can't use this method if you want to do something similar for a row or column. This limits the design of your widgets in certain ways. Since you're having to use multiple values, you also lose certain features like filtering by top N or sorting greatest to least.