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

ibtisamyyy
ibtisamyyy Member
edited March 2023 in Datasets

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?

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    @ibtisamyyy

    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)

    (https://dojo.domo.com/discussion/52687/domo-ideas-exchange-beast-modes-first-last-days-of-the-month-week#latest 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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ibtisamyyy
    ibtisamyyy Member

    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.

  • ibtisamyyy
    ibtisamyyy Member

    Oh Nevermind got it! Thank you guys!

Categories