Slider/Aggregation Puzzle

[#1]

There is a dashboard with a bar chart. Let's say there are five categories that the bar chart is displaying, plus an "other" category. See ASCII example below. A has a count of 1, up through E with a count of five. F is the "other" category.

A | *

B | **

C | ***

D | ****

E | *****

F |

There is also a slider control that contains a "threshold" value. The slider determines which categories (A - E) are moved over into the "other" F category. When the slider is at 0%, you will see the chart above. If the count of a total of a category divided by the grand total of all categories is above the threshold value, it is put in the F category. There are 15 total units above. So if Count A/Count(all) >= Threshold, then the items in the A category are moved into the F category. In this case that would be 1/15 or 6.67%. Once the slider hits 7%, the bar chart should look like this:

A |

B | **

C | ***

D | ****

E | *****

F | *

If you move the slider to 27% (> 4/15), you would see this:

A |

B |

C |

D |

E | *****

F | **********

So, is there any easy way with expressions or set analysis to accomplish this?

[johnw · Accepted solution]

Well, I have something very close. The only problem is that the categories disappear if there's nothing in them. I'm confident that I can solve that too if required, but I think it would take some additional complexity. It's pretty much as simple as using this as a calculated dimension:

=if(aggr(count(ID)/count(total ID),Category)>=Threshold,Category,'F')

[#3]

John,

This is exactly what I was trying to do. It's good that the categories disappear, because that's what I wanted. The point of the slider control is to allow the user to reduce "clutter". In a real world example there might be 100 categories, and this would allow them to lump all of the infrequent categories into an "other" category, and end up with only 10 categories in their bar chart.

I got stuck on thinking that the solution was with an expression. I didn't even think about a calculated dimension. Thanks for the simple solution!

Gary

[johnw]

I assume you already know this, but a more typical approach would be to sort by Y value descending, set a max visible number, and checkmark the "show others (label)". It's not as sophisticated as a user-selectable threshhold, but serves the same purpose of reducing clutter in the chart. To me, it would just depend on how sophisticated my users are.