Filtering - one of the most basic data transformations… or is it?? If you house and process your data in Google Sheets, then maybe not.
Okay, so maybe you know what you are looking for and how to get there, but carrying out a series of filtering steps across multiple columns can be confusing on its own. So the task of replicating this multi-step process in a collaborative space can be extremely tedious.
With Google Sheets reports, stagnant filter views and functional dashboards make the collaboration process possible... But why settle for “possible” when you can have “easy-peasy?”
What if you could carry out complex, multi-step filters across diverse data types all in one easy step and then share it with teammates through an interactive dashboard? With Intersect you can pull your data directly from Google Sheets, set up super filtering blocks with user interactivity and visualize it on a shareable dashboard.
To exemplify this filtering power, I found a generic sales dataset on Kaggle.
Read Data from Google Sheets
First, I must pull my dataset into Intersect. The sales data is housed in Google Sheets, so I use the “Read from Google Sheets” integration block. The raw dataset looks like this:
This table contains order, product and customer information for close to 3000 sales. Intersect automatically recognizes the type of entries in each column which makes filtering across numerical, string and datetime columns quick and easy.
The “Filter data” super block allows users to filter the data according to one or multiple conditions. These conditions are easily adjusted using drop down menus. When a Filter column is entered, the column type is automatically detected and inequalities relating to the column type are automatically populated.
For this example, I want to set up filters for shipping status, customer name, sales $ amount, and date. So with these filters I can identify shipped orders placed by Technics Stores Inc. in the last month with a sales total of at least $3000… big deal, right?
Not quite. In its current state, this filter block performs much like a Google Sheets filter view- an inflexible, filtered snapshot for a specific instance or use.
The real magic (and true value of a data app) comes to light when we publish and run.
But first, let’s edit the input settings so the filters can be adjusted according to our changing needs.
Make it Interactive
While I might be interested in the given filter conditions right now, I also want the freedom to easily change and share results from other filter conditions (we’re leveling up from archaic filter views, remember??).
To do this I need to adjust the in-app interactivity of the block inputs that I want to be editable.
Now, when we run the data app, any input with a blue gear icon will prompt users to enter an input.
In this case, I’ll leave the column inputs established as these are common columns to filter orders by. However, the values in the condition inputs will be adjustable.
I also added a fully adjustable filter condition (all three inputs are adjustable) just in case we wish to filter according to a different column, such as product line.
Before we publish and run the app let’s add a couple more features to increase the functionality of the app beyond simply filtering.
Customize your Results
Data apps are designed to process or manipulate data in a particular way by pulling data through strings of function blocks.
Therefore, we can increase the processing power of our app with each additional block.
Google Sheets filter views users to isolate specific data of interest and share this meaningful data with others by removing excess data or information. So, once again, let’s level up on spreadsheet analysis.
I’ll add a sorting block and a subset block to further allow us to isolate meaningful data and communicate intent to other teammates.
After the dataset is filtered, an interactive “Select subset of columns” block shows only selected columns of interest in the resulting dataset.
And lastly, a “Sort data” block allows us to choose a column to sort our data by.
Finally, let’s publish and run!
Publish, Run and Share
Now, when I publish and run the data app I am prompted to enter filter conditions, select a subset of columns and a sorting column/direction.
The resulting data table contains 3 orders of interest:
Now I can share these run results with teammates via this public sharing link.
Teammates can access the results and are free to interact with and use the app without affecting the data for other users. This way, I can easily share the insights of a particular filter while maintaining the functionality of the app. Unlike the “still shot” approach of filter views, data apps come with replicable functionality.