Pivot Data from Columns to Rows

Pivot data from columns to rows(which is also called as wide format to long format) can be done in Tableau for only Excel sources.

If we use any data sources we need to create the view in database by using multiple UNION's as explained in sql below.

For example, suppose you have the number of devices sold by quarter for three vendors in three separate fields. You can pivot your data so that the vendor is in one field and the number of devices sold is in another field.

Pivot the data

After you have set up the data source, in the grid, select two or more columns. Click the drop-down arrow next to the column name, and then select Pivot. New columns called "Pivot field names" and "Pivot field values" are created and added to the data source. The new columns replace the original columns that you selected to create the pivot.

Add to the pivot

To add more data to the pivot, select another column, click the drop-down arrow next to the column name, and then select Add Data to Pivot. Make sure that the pivot columns and values look as expected before you begin your analysis.

To remove a pivot, click the drop-down arrow next to the name of a pivot column, and then select Remove Pivot.

Troubleshooting pivots

Red fields in the view and fields with exclamation points in the Data pane: Because the original fields are replaced with new pivot fields, any references to the original fields in the view will no longer work. They cause fields to become red in the view or show a red exclamation point next to the field in the Datapane.
Null values in the grid: If all of the original fields used in the pivot are removed, for example in an extract refresh, null values display in the pivot fields.
No pivot option: Pivot appears when you select two or more columns in a single Microsoft Excel, text file, Google Sheets, and .pdf data source. If using a different data source in Tableau Desktop, you can use custom SQL to pivot.

Pivot using custom SQL (Tableau Desktop)

You can also use custom SQL to pivot your data, even if you aren't working Excel, text file, Google Sheets, and .pdf data sources. When you use the UNION ALL operator in a custom SQL query, you can take values from distinct columns and put them into a new column.

For example, suppose you have a table called Contest.

Contest

Runner	Start Time	End Time
Amanda	9/3/2016 3:04 PM	9/3/2016 3:25 PM
Oscar	9/3/2016 3:04 PM	9/3/2016 3:21 PM
William	9/3/2016 3:04 PM	9/3/2016 3:16 PM

To optimize your analysis of this data in Tableau, you can use the following custom SQL query to pivot the "Start Time" and "End Time" columns so that their values are in a single column.

Select [Runner]

, 'Start' as [Action]

, [Start Time] as [Time]

From [Contest]

Union ALL

Select [Runner]

, 'End' as [Action]

, [End Time] as [Time]

From [Contest]

The above custom SQL query does the following:

Pivots the Start Time column header into a string value called Start and adds that value to a new column called Action.
Pivots the End Time column header into a string value called End and adds that value to a new column called Action.
Pivots the Start Time and End Time columns so that their values are in a new column called Time.

The following table shows the results of this custom SQL query.

Runner	Action	Time
Amanda	Start	9/3/2016 3:04 PM
Oscar	Start	9/3/2016 3:04 PM
William	Start	9/3/2016 3:04 PM
Amanda	End	9/3/2016 3:25 PM
Oscar	End	9/3/2016 3:21 PM
William	End	9/3/2016 3:16 PM

To pivot data using custom SQL

Connect to your data.
Double-click the New Custom SQL option in the left pane. For more information, see Connect to a Custom SQL Query.
In the Edit Custom SQL dialog box, copy and paste the following custom SQL query and replace the contents with information about your table:
```
Select [Static Column]
```
```
, 'New Value (from Column Header 1)' as [New Column Header]
```
```
, [Pivot Column Values 1] as [New Values]
```
```
From [Table]
```
```
Union ALL
```
```
Select [Static Column]
```
```
, 'New Value (from Column Header 2' as [New Column Header]
```
```
, [Pivot Column Values 2] as [New Values]
```
```
From [Table]
```
```
Union ALL
```
```
Select [Static Column]
```
```
, 'New Value (from Column Header 3' as [New Column Header]
```
```
, [Pivot Column Values 3] as [New Values]
```
```
From [Table]
```
Where the following is true:
- Static Column: A comma-delimited list of the columns from Table, both dimensions and measures, which should not be included in the pivot.
- New Value (from Column Header 1-3): New names that you give to the original column headers, which are used as row values in the pivot.
- Pivot Column Values 1-3: The columns whose values need to be pivoted into a single column.
- New Column Header: The name you give the new column that contains the new row values from New Value (from Column Header 1-3).
- New Values: The name give the new column that contains the original values from Pivot Column Values 1-3.
- Table: The table that you connected to.
Click OK.

Comments

Add Space between bars in Tableau chart

Add Space between bars in Tableau chart Scenario: Tableau defaults to no spacing between the panes in a view. How do I get some spacing between groups of bars in my charts? It can be achieved through the steps below: Add subtotals (Analysis->Totals->Add All Subtotals) Right click on the measure pill on the Rows shelf and change the default SUM() aggregations to MIN() To edit the color legend – double click on the Total color, this takes you to a color dialog, select the WHITE color, click OK Right click on the word Total in the X axis and select Format In the Format window, click the Header tab and blank out the Total label field The added space looks like below:

Initial Capital for text in Tableau

Init Cap in Tableau Tableau doesn't have an inbuilt function like TitleCase or InitCap or Proper. In order to overcome the issue to display the first letter in capitals below function comes in so handy UPPER(LEFT([[String]],1)) + LOWER(MID([String], 2))

Adding “Apply” Button to A Filter Menu In Tableau

Adding An “Apply” Changes Button To A Filter Menu In Tableau Sometimes when a user is changing the options they want on a filter in Tableau, the chart updates as they change each option. This might not be the best user experience if they are changing many options and don’t want the dashboard to redraw the view until they have completed their selection. It is very simple to fix this – Tableau includes an “apply” changes option, which when enabled, means the dashboard won’t redraw to reflect the new filter choices until the user presses the “apply” button. Simply click the drop down arrow on the filter menu > go to “customize”, then click “show apply button”.

Error "Cannot mix aggregate and non-aggregate arguments with this function"

Error "Cannot mix aggregate and non-aggregate arguments with this function" When Creating a Calculated Field When creating a calculation, the following error might occur: "Cannot mix aggregate and non-aggregate arguments with this function." The issue can be resolved through one of the below options: Modify the calculation so that all fields are either aggregate or non-aggregate. Each option can result in different values (please reference the additional information section for specific examples). Option 1 (Aggregate Then Divide) Wrap all fields in an aggregation. Sample: [Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales]) Option 2 ( Divide Then Aggregate) Remove aggregations from all of the fields. Sample: [Profit] / SUM ([Sales]) ->[Profit] / [Sales] Option 3 ( Condition Then Aggregate) Move the aggregation so all fields are aggregated. For example, the calculation: IF [Row ID] = 1 THEN SUM( [Sales] ) END could...

Removing "Abc" Placeholder Text in Tableau Measures column

Removing "Abc" Placeholder Text in Tableau Measures column Quick and easy fix for this issue could be: Use Polygon mark type On the Marks card in the dropdown menu, select Polygon Resize the last column to make it smaller Navigate to Format > Borders In the left-hand Format Border Pane, for Column Divider, For Pane, select None from the dropdown menu Navigate to Format > Shading In the left-hand Format Border Shading, for Row Banding, move the slider to the desired level of row banding

Tableau Bar Chart Rounded corners

Tableau Bar Chart Rounded corners Follow the steps in the video below to create rounded corners for the bar chart:

Using Special Characters in Tableau URL Parameters

Using Special Characters in Tableau URL Parameters When we try to use special characters in URL parameters, the URL parameter might not do anything, or an error might occur. The issue can be resolve by: Use one of the following workarounds: Replace the special character with the URL encoding sequence for backslash (\) (%5c) followed by the URL encoding sequence for the special character. The backslash is needed to escape the special character. For example, the URL encoding sequence for backslash and comma (\,) is %5c%2c. In the data source, separate comma-delimited field values into separate columns that can be filtered independently. In Tableau Desktop, use a calculated field to replace the special characters, such as commas or spaces, with hyphens (-). Cause The browser cannot parse the special characters used in the URL. Additional Information The error varies depending on the browser and special character being used. Networ...

Cannot mix aggregate and non-aggregate arguments with this function

Cannot mix aggregate and non-aggregate arguments with this function Issue When creating a calculation, one of the following errors might occur: "Cannot mix aggregate and non-aggregate arguments with this function." (Option 1,2,3 or 4 can be used). " All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources." (Option 1 or 3 can be used). "Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated ." (Option 2, 3 or 4 can be used). Environment Tableau Desktop Resolution Each option can result in different values (please reference the attached workbook in the right-hand pane and additional information section for specific examples). Option 1 (Aggregate All Fields) Wrap all fields in an aggregation. Sample: [Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales]) Option 2 ( De-aggregate All Fields) Remove aggregations from all of ...

Set a Date Filter Default to Max Date in Tableau

Set a Date Filter Default to Max Date in Tableau Some of us might have not noticed the setting a filter to default to the Max date. There is a quick and simple option to achieve this in Tableau filters. Follow the steps below: 1. Create a date filter with the date dimension. 3. Drag the date dimension to filters pane. 4. Edit the date filter to Select from the list and select the max date available. 2. Now, y ou have the option 'Filter to latest date value when workbook is opened', check this box and click on OK.

Tableau Extract Update Error "Timeout Error: IPC_NamedPipe::Select(WaitForMultipleObjects)

Tableau Extract Update Error "Timeout Error: IPC_NamedPipe::Select(WaitForMultipleObjects) When trying to update the Data Source or w hen attempting to connect to a data source, or create and/or refresh extracts with Tableau Desktop, the following error message occurs: IPC_NamedPipe::Select(WaitForMultipleObjects): Timeout. Cause Anti-virus software is blocking Tableau processes from running, or the Logs folder contained corrupted information. Resolution Option 1: If it is easy w ork with IT helpdesk/support to add below Tableau folders and processes to exclusions : Tableau.exe hyperd.exe hyperdstarter.exe *.hyper extension (if AntiVirus has an option for extension exclusions) C:\Users\<username>\Documents\My Tableau Repository C:\Users\<username>\AppData\Local\Temp\TableauTemp C:\Users\<username>\AppData\Local\Tableau C:\Program Files\Tableau Make sure that child folders are included in the exclusions. AntiVirus prog...

Tableau Analytics

Search This Blog