HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- how can I dynamically filter out one min score and...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-18-2019
08:13 AM

03-18-2019
08:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-18-2019
11:13 AM

03-18-2019
11:13 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-18-2019
11:15 AM

03-18-2019
11:15 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-18-2019
11:23 AM

03-18-2019
11:23 AM

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

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-18-2019
11:43 AM

03-18-2019
11:43 AM

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

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-18-2019
01:57 PM

03-18-2019
01:57 PM

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

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"

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"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-22-2019
11:24 AM

04-22-2019
11:24 AM

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

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

Announcements