Sort by Top 20

chrissy

Hi,

I am currently looking at a healthcare practice's insurance list, which is hundreds long. I would like to create a chart with only the top 20 most commonly used insurances. Is there a way to do so in beastmode? I've tried chart properties → general → maximum rows (20) but it didn't work. Thanks!

MichelleH

@chrissy Depending on the chart type you're using, that should work as long as you also have your data sorted. Can you please share a screenshot of the analyzer (hiding any sensitive information) so we can help troubleshoot?

TOccon

@chrissy That is a great question! The easiest way to do this would be using “Limit Rows” located just below the filters on the card analyzer. (pictured below). All you need to do is enter 20 and make sure you have the sorting set to what you desire. Another more complicated option is using a transform (Magic ETL, SQL Dataflow, etc.)

chrissy

HI @TomOccon

Screen Shot 2023-03-22 at 11.43.54 AM.png

Thanks for your reply. When I try limiting the rows, it doesn't show the top by count and also shows only the month of January (the first screen shot). I'm not sure what's going on here.

chrissy

Hi @MichelleH -

I currently don't have anything sorted - outside of having the pivot table showing the total value from top down (screenshot included). Is there a better way to do this ? Thanks !!

Screen Shot 2023-03-22 at 11.46.38 AM.png

MichelleH

@chrissy Since you're using a pivot table with different dimensions in the rows and columns, you will need to add the ranking in MagicETL using the Rank and Window tile. Then you can filter the card to only include rows where the ranking is 20 or less.

chrissy

Hi @MichelleH

Thank you for your help. I tried to create the magic ETL but ran into issues. Do you have any suggestions? Thanks!

Screen Shot 2023-03-22 at 4.23.51 PM.png

MichelleH

@chrissy You will need to sort by the sum of the “Total Activity” field from your card (descending)

chrissy

Hi @MichelleH -

Total activity is a beastmode I did, counting distinct appointments. It doesn't show up in the Rank & File option. When I go back into the chart, sort by total activity and limit the row count, it does not capture all the datapoints. Do you have any other suggestions on how to best proceed? Thanks!

Screen Shot 2023-03-23 at 11.33.09 AM.png

MichelleH

@chrissy If Total Activity is a beast mode, then you will need to recreate the calculation in ETL using a Forumla or Group by tile, dependng on whether it includes aggregation

chrissy

Hi @MichelleH -

Thanks for your help. I'm trying to do this in Magic ETL. The total activity formula is : Count(DISTINCT appointment_id). However, it looks like there isn't a formula option for count distinct. When I validate it also shows up as an error. Do you have any suggestions? Thanks!

MichelleH

@chrissy You will need to use a formula in the Group By tile instead of the Formula tile to use a COUNT(DISTINCT ) since that requires aggregating the data.

chrissy

Hi @MichelleH -

Thanks for all your help with this. I created a "Group By" formula to count the distinct appointments (aka Total Activity) and then did a Rank and Window to put the insurance names in ascending orders. When I try and the ETL, it says "Saved but incomplete. Cannot be run until all actions are configured correctly." Do you happen to know where the disconnect is? Thanks!

MichelleH

@chrissy There are two steps involved in the Rank & Window tile: selecting the partitions/order and then applying functions. So far it looks like you've only competed the first step. Here is an article on how the tile works:

https://domo-support.domo.com/s/article/360044876094?language=en_US

In the Rank & Window tile you will want to order the functions on Total Activity (2) descending (3) and partition by insurance_name (3). Then click "Apply" in the top right corner and then create a new field using the Row Number function. This is the field that you will use to filter your card to the top 20.

chrissy

Hi @MichelleH -

Thank you for the video, I found it very helpful. And thank you for your patience. I tried to do what was suggested but I'm still unable to run or preview the dataset. Below is how I set it up if you have any advice. Thanks!

MichelleH

@chrissy Of course! Have you named the output dataset? In your earlier screenshot it looked like you had not named it yet, which would prevent it from running

chrissy

@MichelleH -

Thanks! That worked! I was able to run it! Unfortunately, I am still running into some issues. When I try to recreate the graph and have it sorted by the top 20 (by value) it still doesn't show the accurate results… do you know why ?

MichelleH

@chrissy Instead of using the "Limit Rows" feature in analyzer, add a filter to your card using the Row number field you created in your ETL to only include rows where the row number is less than or equal to 20

chrissy

Hi @MichelleH -

Thanks for your reply. The issue to limiting rows based on a number (vs. a top 20 ranking) is that charts will go on different dashboards for clients, who each out have a different number of insurances. For example, Client A may have 10k insurances in their number 20 slot, where client B may have 400. Is there any way to do this? Thanks!