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

WizardOz Contributor

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.




  • guitarhero23
    guitarhero23 Contributor

    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:

    Dojo Help 20.JPG


    Dojo Help 21.JPG


    In a MySQL dataflow you could do it this way

    Dojo Help 22.JPG



    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



    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • 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.

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • WizardOz
    WizardOz Contributor

    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.





  • 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:


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
This discussion has been closed.