I too had previously tried that, and upon looking at the SQL Tableau is generating, from the log file, I don't think there is a way to get the windowing function effect you are looking for.
I prefer to preprocess data for Tableau, or use custom SQL upon connection.
Just take the double-double-quotes off the '%1' and move the argument after the closing quotes for the SQL expression, like this:
FROM [factTable] INNER JOIN [Location] ON [factTable].[Area Code] = [Location].[Area Code]
WHERE [Location].[Market] = %1
In general, RAWSQL calculated fields are intended to operate within the same level of detail as the rest of the visualization you are building. As Joe described, RAWSQL calculations aren't intended for windowing expressions such as ordered analytics and subqueries.
Since SUM(Sales) by Market is a trivial visualization to create, I assume that you are trying to incorporate this aggregate information into a larger visualization? Can you describe what you're trying to accomplish? Typically when you need to mix levels of detail -- for example, to compare average sales by state against the average sales for the entire region -- you will need to create two separate visualizations and link them in a dashboard. See the view below for an example.
> RAWSQL calculations aren't intended for windowing expressions such as ordered analytics and subqueries.
Intended for it or not, they can be really useful - I use that sort of construct a lot.
But it is also very, very easy to end up with obscure SQL errors or (and this is much worse) no SQL errors but the wrong answer, through inappropriate mixing of levels of detail. I guess that is the sort of reason for cautioning against them, is it, Robert?
Personally I'd position this is a "don't play with matches" or "don't run with scissors" type of recommendation, rather than a "thou shalt not kill" commandment.
We make no guarantees that the compiled SQL expression will be well-formed or well-behaved if you use subqueries in a RAWSQL calculation. You are also very much at the mercy of your particular data source, and they all seem to have different restrictions on the use of subqueries.
I should upgrade my recommendation to "don't juggle with chainsaws". ;-)
I use RAWSQL extensively to do exactly the sort of thing you describe. I think my very first posting on the Tableau forums was a question about analysis at multiple levels of aggregation - and I've found using RAWSQL one of the most effective ways of addressing that issue. I understand Robert's cautions about the vagueries of the different back-end databases, and I also understand the limitations of my own SQL knowledge - so I'm always careful to verify my answers - but I still believe it's an invaluable technique. I'm pretty sure I used it for the workbooks I posted in these two recent threads, for example.
On reflection, I would position this somewhere between "running with scissors" and "juggling with chainsaws" on the risk continuum. Personally, I don't juggle chainsaws, but I do use RAWSQL to do correlated subqueries. I did nearly cut my thumb off with a circular saw once, but that's another story... ;-)
My view is that as long as you understand what you are doing it's a valuable tool in the tool kit.
Thank you for posting the workbook at:
Both your's and Richard's postings prompted me to reevaluate my stance on the use of RAWSQL functions.
My number one reason for not using RAWSQL is because it breaks when you extract the data. I will use a RAWSQL function when the data source is a live database like SQL Server and the data cannot be preprocessed, as the RAWSQL subquery will only run when that calculated field is in use, vs a custom SQL that runs every time.
If the data will be extracted, from my experience, it is a chore to rewrite the RAWSQL function , it can be done, but if another level of aggregation is needed, and I prefer to preprocess the data or write custom SQL, as the subquery will be only be run to generate the extracted data, and not during each refresh of the visualization.
My number one reason to use RAWSQL is to make use of a function not available in Tableau, and the data source will be a live database. for example, geometry functions when connected to SQL Server, or actual windowing functions from the source database.
I feel that the key in using either RAWSQL, custom SQL, or preprocessing is knowing how you are going to use the generated values, the effects of filtering and the level of aggregation Tableau will use to display marks.
The following factors influence my decision on what route to choose:
- Is the data source a flat file or a live database?
- Will the data be extracted?
- Can I preprocess the data?
- Do I need a function not available in Tableau?
My general preference is to preprocess data, or use custom SQL and then extract, and only use RAWSQL when there are constraints. That is why you will frequently see me recommend preprocessing or custom SQL on the forums, as in the above link.
Richard, thank you for spotting the issue with the syntax. I can't believe I couldn't see it before - it is so obvious.
Robert, I understand the danger of "juggling with chainsaws", but what I found missing in Tableau is the ability to access aggregated values beyond the scope of the cell in the worksheet. For example, if worksheet shows sales by market by product, then any custom calculations in that worksheet's cells will be for a particular product in a market, i.e. limited to the cell's scope.
In other words, your statement
"RAWSQL calculated fields are intended to operate within the same level of detail as the rest of the visualization"
describes the exact problem I am trying to circumvent.
Tableau's ability to combine several visualisations into a dashboard and link them is fantastic and I use it a lot. But quite often I have the need to display, for example, average sales for a product in all markets in the same viz with actual sales by product by market (see attached screenshot).
I could not find an easy and straightforward way of doing it, hence the experimentation with raw SQL.
I am only experimenting at this stage and I am not sure if I will be comfortable using this approach in production, considering the warnings in this thread.