Pass me that SQL – using rawsql to unlock missing functionalities

Rawsql is – in my opinion – one of the most often overlooked little gems in Tableau.

I do not really know why that is, maybe the descriptions look a little to programmy with the argument placeholders?

Whatever the reason, this little exhibit hopefully will serve a little good to this topic.

When to use rawsql?

Rawsql can be your go-to-resource whenever you are missing a functionality in your data source connection that you need.

Examples that are quite often encountered are

  • Median
  • ISO Week
  • Random number calculations

All rawsql share the combination of the actual sql query (mostly w/o “SELECT… FROM”) and the argument part. When copying a sql statement or parts thereof into the rawsql calculation in Tableau, make sure you a) put the query in quotes and b) include %1 for argument 1 and %n for your nth argument where argument basically means whatever you want to pass into the rawsql query, i.e. any field from Tableau data pane, could be a measure or a dimension.

Median

It can look somewhat daunting at first but let’s have a look at what you would need to do in order to derive a median of a specified measure

This is what you will get when being connected to bigQuery

Now let’s say you want to calculate the median, you can use the following option:

RAWSQLAGG_REAL("APPROX_QUANTILES(%1,1000)[OFFSET(500)]", [my test measure])

Please take careful note of the fact that I am using RAWSQLAGG_REAL and not RAWSQL_REAL – both exist in Tableau but only the first one will work. Using the second will leave you with this statement

If you use the latter, Tableau tries to aggregate what factually already is an aggregate and this fails it. If we use rawsqlagg we also see on the view that our calculation is recognized as an aggregate from the outset (AGG(Calculation1))

Quite a difference between using average and median, isn’t it?

ISO WEEKS

Now let’s say we want to check iso-weeks.

Contrary to the afore, we now will need to consider a date field in our rawsql expression and a date is a dimension in Tableau. This means we will face a slight deviation.

Assume we want to create a new field that will give us the iso-weeks like this: W-1, W-2, W-3 etc

RAWSQL_INT('EXTRACT(ISOWEEK from %1)', [date])

Note how this time I am not using the RAWSQLAGG_INT but RAWSQL_INT because I do not want to aggregate anything. Instead, all we want is the corresponding (integer) isoweek number for every date in our data set.

Since this usually will be a dimension, I recommend you right click your calc on the data pane and select “convert to Dimension”. Then, right click it again, go to “Default Properties –> Number Formats” and go to custom. Here, insert

"W-"#

and you will get this:

Random number

For the last part, let’s assume you need to create random numbers between 0 and 1. A usual example is a jitter chart where you make use of random to optically de-aggregate the different dots.

An undocumented option is to use “random()” directly in Tableau but this will only work on connections that support it, such as extracts.

But, sticking with bigQuery, this is not possible.

So our workaround here is to use:

RAWSQLAGG_REAL("rand()")

For fun’s sake lets also say we want to create full (integer) random numbers between a parameter defined upper and lower bound.

First, create the two parameters and then use this

RAWSQLAGG_REAL("SELECT round(%1 + RAND() * (%2 - %1)) rand_between", [p.random_lower],[p.random_upper])

With this, we can now create random results that are somewhere between whatever we set up in our parameters.

Combined, these two calcs give us the following.

And that’s it for today. I hope you found that useful.

As always, appreciate your feedback.

Until next time.

Steffen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s