How to set Maximum Items on a bar chart for the series data?

atcaldwell
atcaldwell ⚪️
in Card Building

I have a vertical stacked bar chart with date (month/day) on the x-axis and the number of API calls per individual user as the series. Because I have a large number of users with very few API calls and a few users with a large number for each of them, I want the stack bar segments to just reflect the top 10 individual users with highest API call volume and then lump all others outside of the top 10 into an 'Other' bucket.

The Maximum Items setting in General chart properties does not do want I want. It is instead limiting the chart to 10 daily bars with an 11th bar for all other days.

How to I get Top 10 users with highest API call volumes and then all other users as a single segment of the stacked bar?

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔴

    One way to accomplish this would be to use the rank and window tile in Magic ETL and get your totals and rank them in order. You can then use the rank to show your top 10 and then use a beast mode (or formula tile in Magic ETL) to assign anyone above 10 to the other group. Use this new field as your series.

    You can also use window functions in beast modes, but you need to ask your CSM to turn it on and then write a rank and sum window function to do what you would do in the ETL.

  • GrantSmith
    GrantSmith Indiana 🔴

    I agree with @MarkSnodgrass - You'd need to aggregate / rank the data before pulling it into your card to filter on the rank.

    Window functions are available in a beast mode however you can't filter based on an aggregate function in analyzer (unless you have a very alpha option to filter on aggregates but I don't recommend it as it's can have unexpected results and doesn't always work as expected)

Categories