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

ibtisamyyy
ibtisamyyy ⚪️
in New to Domo

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 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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷

    @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 ⚪️

    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 ⚪️

    Oh Nevermind got it! Thank you guys!

Categories