While working with Tableau, we frequently require referencing a “fixed value” or a “constant value” in an expression regardless of the “filters” applied to the visualization.

As an example, figure 1 below displays each Product Sub Category along with their respective sales values.  The second column displays the total percent of individual sales value against the gross total of 2,297,201. Since the view is filtered by Product Categories, it will show the respective Sub Categories along with their respective sales whenever a filter is applied. However, the percentage of total value should still be calculated on the gross total of 2,297,201 regardless of whether or not the list displays all of the Product Sub Categories.

Figure 1

Example_FixedValues

 

There are four distinct approaches to solving this problem:

a)      RAWSQL function

b)      Fixed LOD expression

c)      Table calculations as a filter

d)      Data blending

RAWSQL Function

RAWSQL are called as pass-through functions and used to write SQL Statements inside a Tableau calculated field. These SQL statements are not interpreted by Tableau and are directly passed on to the underlying database.

The calculated field in Tableau will resemble the following:

[preformated]RAWSQLAGG_REAL(“SELECT SUM(Sales) FROM [Orders]”)[/preformated]

This calculation will send the SQL Statement to the underlying database and return a “real” or “float” result. This result set is not affected by the filters applied in the visualization.

Fixed LOD Expression

Since the Fixed LOD expressions are evaluated before the dimension filters are applied to the view, the result is retrieved even before the users interact with the view by selecting the filters.

The calculation will be displayed as:

[preformated]{FIXED : sum([Sales]) }[/preformated]

The calculation does not specify any dimensions, hence the result becomes the gross total sales.

Table Calculations as Filter

Table calculations are performed by the VizQL component of Tableau after the query results are retrieved from the database. As a result, when a filter is applied, the query output is retrieved and the table calculations are performed.

The lookup table calculation can be used as followed:

[preformated]Lookup(Attr(Sub-Category), 0)[/preformated]

The relative position in this function is 0, which always returns the value of the category at the current position (defined by the scope and direction of the calculation).

This expression can be used as a quick filter. When users interact with this filter, Tableau simply shows or hides the members from the view without filtering the view and without affecting the totals.

Data Blending

The data blending approach uses a “self” blend option that combines the same dataset with itself. First, a duplicate of the data source is created, then it is blended with itself. While blending, no columns are specified for linking the two data sets.

The applied filters will only affect the primary data source, making the SUM(Sales) from the primary data source evaluate with respect to the sales of the product sub-categories. Since the filters are not applicable at the secondary data source, and no column or dimension is specified as a link between the data sources, the SUM(Sales) from the secondary data source will always be the gross total.

The table below compares each of the options:

Parameter RAWSQL function Fixed LOD Table Calculation Data Blending
Supported by all databases? No No Yes(Processed by Tableau) No(Cube databases cannot be secondary source)
How will it affect performance? Depends on DB(Subqueries will have to be handled) Depends on DB(Subqueries will have to be handled) Depends on #marks in the view (VizQL has to process it) Multiple I/O will take place at DB to process separate queries from Primary and Secondary data sources. The “blending” of query outputs will be done by Tableau locally
Change in syntax/query/ expression? Yes (depends on underlying database) No No No

Deciding which option to use will depend on the database capabilities, the complexity of the expressions, and the number of marks presented in the chart.

 

This Tableau Tip was written by:

Sourabh Dasgupta

Sourabh Dasgupta is the Director of Technology & BI at Corporate Renaissance Group India and is also the Product Manager of CRG’s in-house software products such as FlexABM and Cost Allocator. Over the past 14 years, he has been involved as a technical expert in various projects and assignments related to the implementation of Activity Based Costing and Business Intelligence and reporting systems in India as well as South Africa.

He is one of the professional trainers for Tableau software and has conducted many sessions for clients. He has been involved in designing and development of Tableau reports and dashboards and was awarded a Tableau Desktop 8 Qualified Associate certificate as well as a Tableau Server 8 Qualified Associate certificate.  He has successfully conducted training on the Tableau Software (Desktop and Server versions) for many clients, which included basic and advanced features of the Tableau Desktop and Tableau Server.

Sourabh has a Bachelor of Science degree (Computer Science, Physics, and Mathematics) from Nagpur University, India and a Diploma from the National Institute of Information Technology (NIIT), Pune, India. He also holds a Microsoft Certified Professional certificate for Designing and Implementing Desktop Applications with Microsoft Visual basic.

If you wish to accelerate your skills and empower your learning with hands-on education or are seeking an Expert-on-Demand, reach out to Sourabh at sdasgupta@crgroup.com

You might also like to check out: