Using Filters to Change the Group By Field

We can leverage filters to even change the field we are grouping by! This technique allows users to effectively see many different analyses, all in one query.

Below we have a query looking into the number of users by month for a fictional gaming company. Notice the [group_by] filter call in the select statement:

select
  [created_at:month]
  , [group_by]
  , count(*)
from
  users
group by
  1
  , 2

Here is what our "group_by" filter looks like. 

 

To group by platform, we can select "platform" in our group by filter. Below are the results:

To group by source, select "source"

 The same applies for "gender."

 

Protip: You can call the filter in your chart title so the title can dynamically describe your filter selection!

That's three different analyses we were able to do by writing just one query! Creating group by filters is one of the many ways an end-user who is unfamiliar with SQL can dive into the dataset. Happy Periscoping :)

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • what if we would like to see cumulative? without group_by? Can we handle this in the same query?

    We had similar problem in aggregation filter. What if we would like to see all times without time periods? There is no option for that if range exceeds a year.

    Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 1 yr ago
      • 2
      • Reported - view

      Himmet Kaplan We can add a default value for the group_by filter so it shows all values, without segmenting any data

      select
        [created_at:month]
        , [group_by|1]
        , count(*)
      from
        users
      group by
        1
        , 2

      Then, we also want to ensure we are hiding the legend when we are only showing one series. Effectively, we have made the default group_by a column with '1'. Since all values belong to this filter, the bars will not be segmented when no value is selected in the group_by filter

       

        

      Let me know if this is what you're looking for!

      Reply Like 2
  • I found this post super helpful.
    With the help of the above comments I was able to create a second filter so that the data could be cut by 2 or 1 group by's.

    These were the filters I created to cut our weekly spend by project name and outstanding vs paid payments.

    I then used a case when statement to come up with the series labels. The case when statement got upset when it was comparing an integer to a string so I used the same hack as above but having the default be "1" as a string rather than 1.

    Feedback is always appreciated if there's room for improvement.

    Thanks!

    Reply Like 3
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 1 yr ago
      • 1
      • Reported - view

      Alejandro Perez I really like how you leveraged those case whens to create the series label. Clever! It looks like the SQL was also looking for strings rather than integers because we are running Concat, which is a string function.

      Reply Like 1
Like3 Follow
  • 3 Likes
  • 1 yr agoLast active
  • 4Replies
  • 882Views
  • 5 Following