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

ibtisamyyy

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

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.

GrantSmith

@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.

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

Oh Nevermind got it! Thank you guys!