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
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 567 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 600 Beast Mode
 14 App Studio
 29 Variables
 596 Automate
 150 Apps
 417 APIs & Domo Developer
 28 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 362 Distribute
 99 Domo Everywhere
 261 Scheduled Reports
 2 Software Integrations
 98 Manage
 95 Governance & Security
 14 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive