What you need is a union, and that can either be done with an ETL process, or with custom SQL. For example, if there were two text files in the same folder directory, then something like:
SELECT NULL AS [Animal Name], [Historical#txt].[Group1] AS [Group1], [Historical#txt].[Respresentation] AS [Respresentation], [Historical#txt].[Snapshot date] AS [Snapshot date] FROM [Historical#txt] UNION ALL SELECT [Current#txt].[Animal Name] AS [Animal Name], [Current#txt].[Group1] AS [Group1], 1 AS [Respresentation], [Current#txt].[Snapshot Date] AS [Snapshot Date] FROM [Current#txt]
would allow you to create the attached workbook. What I have done is taken the custom SQL Tableau generates for each table, and combined them, modified by:
- adding the
UNION ALLstatement between to stack the data, appending the two tables
- adding a field for "Animal Name" to the historical table with a value of NULL for each
- adding a field for "Respresentation" to the current table with a value of 1 for each
In Tableau, I created a calculated field to get the same case for the Group1 values, using the UPPER() function and setting an alias. Then with a quick table calculation on the "Respresentation" value, with a Compute using set to "Group", you can have all your percent of totals in one chart.
union_pct_of_total.twbx 13.6 KB
I'll try it and see where I get. Thanks.
Ok. I get it. Stack the data. Makes sense. Thanks.
One question: can this be done when it the respective data are in separate Excel files (not separate Excel Tabs, but files)?
Tableau can treat one Excel file as a database, and the worksheets/named ranges within that Excel file as different tables, just as it treats text files in the same directory as separate tables within a database. There is no native way to write custom SQL that joins data from separate Excel files when connecting. You may want to look at an ETL process for preparing your data for Tableau.