how to set up a filter based on the count of a non-numeric field

WizardOz

I wanted to create a table chart to list all users who havenot viewed certain number of files.  I have a beast mode to get the number of files viewed using count(file_id).  However, I could not use the beast mode as a filter?

 

Can any one give me some ideas?

 

Thank you.

 

Olivia 

guitarhero23

Aggregations aren't allowed as quick filters so you wouldn't be able to use count(file_id) as the beastmode as you experienced.

 

Is the certain number of files static or is it something that is constantly changing or that you would be curious about and want to change freely? 

 

In an ETL dataflow you could get that by doing the following:

 

 

In a MySQL dataflow you could do it this way

 

 

However I'm willing to bet you have a bunch of other metadata in each row that you want to be able to use and it would be tough with aggregation, so you would want to basically have all of those rows and then join the total file views number to the end of each individual row so that you could select a certain number threshold and still be able to see all the other columns as well. This would just mean that each row (depending on how your data is organized, probably at least by person by file_id) would have the same number at the end. Then you would use that column as the filter. If i'm not being clear maybe share some of the other columns or a mocked up screenshot of what you think it would look like and i'll edit my dataflow to match

 

 

ST_Superman

I agree with @guitarhero23 ... If you need to be able to filter by the count of files viewed, then you will have to add the count to your dataflow.  

 

However, this will not allow you to change the date range or filter for different collections of files.  I might suggest that you sort the visual by the count of files viewed in descending order, then you could just ignore any data below the number you want to exclude.  For example, if you wanted to have a stack ranking of who has viewed the most files this month, you would create a vertical bar chart with users on the y-axis and your beastmode on the x-axis.  You would sort it by your beastmode in descending order and then on the data table, display only 20 rows.  This would give you the top 20 users.  If you change the date range to this year, the graph would update to show the top 20 this year.

 

Each solution has it's limitations.  If you are always looking at the number of files viewed for a set period of time (i.e. last 30 days, current year, etc.) and you always want to include the same set of files to count, then go with the solution provided by @guitarhero23  and you can filter with the newly created field.  If you need to be more flexible with the date range or selection of files, then you will not be able to filter, but will need to sort instead.

WizardOz

Hi, Superman, GuitarHero,

 

Thank you for your ideas.   Like SuperMan said, they both have limitations: 

- I do need to allow users to select date range, country, so using the flow to get the count won't work. 

- Sorting the count descendly, the problem is I do not know the top #, it changes all the time. 

 

I put an exchange ideas to allow beast mode to be filter a card.

 

Thank you.

 

OZ

 

    

ST_Superman

Just to be clear, the limitation is not that it is a beast mode.  You can certainly use beastmode calculations as a filter.  The issue is that you cannot use an aggregate function as a filter.  That is true whether the aggregate function is a beastmode or not.

 

For example, if you wanted to filter out any customer with less than $100 in sales...

What you can do is filter out any single sale less than $100

You cannot sum all of the sales for a customer and then filter out any customers whose sum is under $100

 

If your Sales $ field is in the data set already or if you used a beastmode to calculate sales $

`Unit_Price` * `Quantity`

you could still filter the individual sales out.

 

What you can't do is filter by this beastmode:

sum(`Unit_Price`*`Quantity`)