how can I dynamically filter out one min score and one max score in a dataset per quarter
basically, its a survey dataset that have a score of 0 to 10..
every quarter, we need to exclude 1 lowest and 1 highest.
we may have multiple lowest or highest, we just need to pick one of each for the quarter.
for example,
for January, we have the lowest of 5 and highest of 9. so we exlcude one 5 score and one 9 score.
for February, we have the lowest score of 2 and the highest of 9 still. so we exclude the 2 score now and bring back the 5, and just keep the 9 we excluded ofr January.
for March, the lowest is 4 and the highest is 10, so we still exclude the 2 score from February, and exlcude the 10 for march.
in summary, we excluded the lowest score for the quarter which is 2 and the highest which is 10.
I am wondering if anybody has done it in beastmode?
currently we are doing the manual exclusion of the lowest and highest, we're wondering if we can do it in beastmode
Comments

in your example for February, do you exclude all the 2's or just one of them?
0 
if there are multiple 2's, we filter out only one of the 2's.
0 
I've had a similar delimia, and couldn't figure it out either. I've used the RANK function in an ETL, but the problem is it would flag all the 2's with the same ranking. if each entry has a time stamp, then say MIN of rank, which at least sections out your 2's, and then MIN of time stamp. there's also a lot more functionality in the MySQL dataflows.
0 
actully, I as well have a weekly forecast that's done through a MS Sharepoint form, similar to a survey. whenever someone makes a mistake, we ask they resubmit their entry. I then filter out the original submission with the use of a MIN/MAX grouping in an ETL, which recognizes the first of the two time stamps and excludes it. I know that's only a 50% answer to your problem, but there's got to be a way.
0 
In addition to finding the min and max value for each quarter, I'd also a assign a random value in a new column for all the rows, and then find the rows with the min/max random values. Then, given the min and max random value of the min and max response value, you could then use to filter just the two rows you want to exclude.
I don't know off hand if you can do random values in magic ETL, but you could do all of this in a SQL dataflow.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 
Here is what I got.
(SUM(`surveyscore`)  (MIN(`surveyscore`) + MAX(`surveyscore`)))
/
(COUNT(`Survey Date`)2)I tried it and it looks like it is doing it's job...
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 285 Workbench
 4 Cloud Amplifier
 5 Federated
 2.7K Transform
 91 SQL DataFlows
 577 Datasets
 2.1K Magic ETL
 3.5K Visualize
 2.4K Charting
 619 Beast Mode
 26 App Studio
 33 Variables
 609 Automate
 155 Apps
 421 APIs & Domo Developer
 31 Workflows
 2 DomoAI
 29 Predict
 12 Jupyter Workspaces
 17 R & Python Tiles
 372 Distribute
 103 Domo Everywhere
 266 Scheduled Reports
 3 Software Integrations
 100 Manage
 97 Governance & Security
 4 Domo University
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 91 Community Announcements
 4.8K Archive