how can I dynamically filter out one min score and one max score in a dataset per quarter

WorldWarHulk
WorldWarHulk Member
in Magic ETL

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

  • Sweep_The_Leg
    Sweep_The_Leg Member

    in your example for February, do you exclude all the 2's or just one of them?

  • WorldWarHulk
    WorldWarHulk Member

    if there are multiple 2's, we filter out only one of the 2's. 

  • Sweep_The_Leg
    Sweep_The_Leg Member

    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. 

  • Sweep_The_Leg
    Sweep_The_Leg Member

    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. 

  • AS
    AS Coach

    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"
  • WorldWarHulk
    WorldWarHulk Member

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

     

     

Categories