Filter Data by Disconnected Dataset

by martin.matejka@keboola.com

Sometimes you need to filter data in report based on the value in a metric. Imagine you have a table with Companies and their Revenue.

 Company Revenue
 Puman 89000
 Adidos 256000
 Najk 184000
 Redbook 26000
 Old Balance 95000

Now you want to put them in some buckets, i.e. <50000 is bucket 1, <100000 is bucket 2 and >=100000 is bucket 3. To show an appropriet bucket next to the revenue isn't a hard task. Just use CASE statement in a new metric. 
The result would look like this:

 Company Revenue Bucket
 Puman 89000 2
 Adidos 256000 3
 Najk 184000 3
 Redbook 26000 1
 Old Balance 95000
 2 

When you have buckets, you can also assign them some names in the conditional formatting, like Bad, Good and Awsome. Report looks good, but you decide, that showing all the bucket together doesn't fit your needs and you want to show only specific bucket which you choose in a filter on dashboard.
One way is to use calculated attribute in GD, but they work like...well, they just don't work that good. 
The other way is to use Disconnected dataset.

For our purpose the output table in SAPI would look like this:

 Value BucketName
 1 Bad
 2 Good
 3 Awesome

In writer you set Value as fact and BucketName as attribute. When you have this new dataset in GD, you're just few steps from your desired dashboard.

First you need to create metric from the new Value fact. Something like SELECT MIN(Value) BY ALL OTHER. Let's call that metric _Value.

Next step is to create filtering metric that slice the data in report based on a selection of a BucketName. It will be SELECT Bucket-_Value. When you imagine some calculations between those metrics, you'll find out that subtraction of a Bucket and appropriet Value will result in 0. We can call this metric _Filter_Revenue.

As you (maybe) know from the work with Disconnected Date DImensions, now you need to add a filter to the report, so it reacts to the BucketName selection.
In the report, go to Filters, choose Numeric Range Filter. Use _Filter_Revenue as a metric and set that it should equals to 0.

Place the report on dashboard and add a drop down one value filter using the attribute BucketName. Now when you choose for example Good, GD do the calculation Bucket-_Value and the result without applied report filter would look like this:

 Company Revenue Bucket _Filter_Revenue 
 Puman 89000 2 0
 Adidos 256000 3 1
 Najk 184000 3 1
 Redbook 26000 1 -1
 Old Balance 95000
 2 
 0

But we've stated in the report filter, that we want to show just those data, where _Filter_Revenue = 0. So the result when selecting Good in a drop down will be:

 Company Revenue Bucket
 Puman 89000 2
 Old Balance 95000
 2 

Bam! Happy disconnected filtering!
Comments