Trying to figure out how to filter out top 20th percent of data by week in domo graph

I'm trying to eliminate outliers from my dataset (top20% highest score let's say) by week. So I'd need to filter out (by rank) top 20% of scores from each week and then graph it on a line graph. My data is dynamic and it changes every day so I can't rank the entire data set. I need to rank by week. I've seen people talk about doing this in ETL however I don't see how that is possible.

Can you please help?



  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    You could use the Rank & Window tile in Magic ETL to rank your entries. Once they are all ranked, you could use a group by tile to get the max value value of the rank and then join it back to your ranked entries and then use a formula tile to determine what percentage it is. Then include/exclude on that percentage value.

    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • GrantSmith
    GrantSmith Indiana 🥷


    Since you're wanting to do it by week you'll need to calculate the week number or the start of the week as a new column and then partition by that value in your Rank & Window tile so you are ranking per week. You can use a formula tile and the following formula to calculate the first day of a week:

    DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`) - 1) DAY)

    ( for reference)

    Also, since you're doing this each week you can just set your DataFlow to run once a week.

    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**
  • Thanks, i can't set my data flow to run once a week it runs whenever the data set is updated. Thus I need this to work dynamically. I've managed to rank by week, each week has a ranking, however still struggling to get them to have a percentage instead of ranking.

  • Oh Nevermind got it! Thank you guys!