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