ad

Adding color to Grand Total in Tableau Desktop 9.3

First of all I would like to thank my colleague “Sarita” who corrected all my grammar mistakes and made this post more meaningful.

Highlight Table, prior to Tableau 9.3, would look as follows:


In the above image you can observe the marked cell which obviously contains the highest value of that table. As the Grand Total of a table will be its highest value, therefore, the cell containing it would be in the darkest shade of green color. But this is indirectly turning out to be a disadvantage because no individual value will be as high as the grand total. Hence all the other cells will be in a much lighter shade of green color. This defeats the main objective of the highlight table as it doesn’t display the comparisons between the values appropriately.

To overcome this drawback, Tableau has introduced a new feature of excluding totals from color coding in Version 9.3

The above image will now look as follows in Version 9.3



This image makes more sense than the previous one, significantly.

Now, I wish to see all positive numbers in green and negative numbers in red colored cells.

To achieve this, click on Color shelf and then Edit Colors then select Stepped Color and enter 2 (refer following image). Click OK.




Now the view changes as follows



This looks more meaningful but it would be more appropriate if the color coding extends to the grand total also. You can notice that there are negative numbers in Grand Total also.


Now the question arises, how can we add color code to Grand Total as well?

It is pretty simple as we have an option for this in Version 9.3

You can see this option once you open Edit Color window (refer following image).



Now the Highlight Table looks as follows:





















Voila!!! It’s done.

Learning any subject is not as important as the journey of gaining knowledge …
How you learn and what you learn is more important as it determines your mastery on that subject

Good Luck!

How to calculate time difference between In time and Out time in HH:MM:SS format?

Calculating time difference in Hours between two Date Time columns is easy. But Calculating Time Difference and showing that in HH: MM: SS format needs some logical knowledge.



In Tableau It's easy to get time difference in hours between two date times. Using below formula.

DATEDIFF('hour',[In Time],[Out Time])

Similarly if you want time difference in minutes then you can use below formula

DATEDIFF('minute',[In Time],[Out Time])

for time difference in seconds use below formula

DATEDIFF('second',[In Time],[Out Time])

Lets assume we are trying to find difference between 8/26/2015 9:30:26 Am and 8/26/2015 6:41:48 PM

Step 1:

Find the time difference in seconds use below formulas

Duration in seconds:

DATEDIFF('second',[In Time],[Out Time])

I got 33,082 seconds for given date times above.

Now we need to convert these seconds into HH: MM: SS format.

Step 2:

1 hour = 3600 seconds

33082/3600 = 9.189 hours. That means 9 hours and few more minutes is there.

Now take only hours part. That is 9.

Lets write calculation for this

[Duration in seconds]/3600

Above formula will give 9.189

But how to get 9?. Shall we use Round()?

If I use Round function, Suppose my calculation return hours as 9.551 then round will convert this to 10 which I don't want. Then which one is right function here?.

I Suggest Floor function which will give 9 what ever is there after decimal point.

Floor([Duration in seconds]/3600) will give 9. But I want to show as HH format.

How to archive HH format when we have only single digit?

If your answer is concatenating “0” using logical statement “if” which will impact performance.

Then try this below formula

Hours:

Right(STR(Floor([Duration in seconds]/3600)),2) which will give 09

Step 3:

Now we need to write calculation for MM

33082/3600 = 9.189 hours now we have to take remainder of this. So that you will get the remaining minutes after converting the hours.

To get remainder in tableau we have to use “Modulo” operator (%)

Minutes:

Right(STR(Floor(([Duration In Seconds]%3600)/60)),2) which will give 11

Step 4:

finally we need seconds, left after converting HH:MM.

Seconds:

[Duration In Seconds]%60

Step 5:

Concatenate all these HH , MM and SS to show as HH:MM:SS

Duration:

[Hours] +':'+[Minutes]+':'[Seconds]

Do you feel that you are Tableau Expert? Then try this challenge 7

Here I am coming with real time challenge. Those who are interested can try this and test your knowledge.

My challenges are not too tough to solve. Very simple and will take few minutes. Getting idea and logic to solve these challenges may feel you tough. After finding solution to my challenges people will feel something they learned or something they understood differently. Let's see now how you will think about this challenge.

Look at below image.


Here I am comparing Sum(Sales) for selected Sub Category vs Other Sub Category in South Region by each Year. Now the challenge is how you will do this in tableau.

Note: Please Download the workbook attached below in this post to solve this challenge

See below images for more examples.

Example 1:


Example 2:


To Solve this challenge please download below workbook:


For more challenges: Click Here

How to Show Pie Chart for Customers as Top 10, Top 11-20 and Others

I want to show pie chart look like below image.



Lets see how to build this chart.

Step 1:

First we need to find out Top 10 Customers by Sales. Right click on Customer Name and create set as “Top 10 Customers”.



Step 2:

Now we need to create one more set for Top 20 Customers. Right click on Customer Name and create set as “Top 20 Customers”.



Step 3:

I want to show Top 11 to 20 Customers. To Archive this we need to create Combined Set based on “Top 10 Customer” and “Top 20 Customers”. Right Click on “Top 10 Customers” Set and create combined set.



Here I am excluding Top 10 Customers from Top 20 Customers. So that I will get Top 11 to 20 Customers.

Step 4:

Now Create a calculation field to divide the Customers into these 3 groups. Name the Calculation field as “Customer Group”



Step 5:
Drag “Customer Group” into Rows and Sum(Sales) into Columns.


Step 6: You will See Bar Chart by Default. Now go to Show Me and select Pie Chart. Turn on the Labels by clicking “Abc” icon.

Tableau 9.3 New Features

Union:

Combine data that have been split across multiple files or tables into a single Tableau data source.

Note: This Union Feature available only for Excel and Text based data source.

When to use this Union Feature?



For example see above image. I have city wise bike trips data in files. Each city data stored in single file. But I want to analyze this data for all cities. Then you have to copy this data from each file and prepare new file by combining all this data. Instead of doing this you can directly combined this files using new Union feature in tableau 9.3

Similarly some times we will collect monthly data in separate files. In that scenario also we can use Union feature all combine all months data into single data source without using copy and paste.

Data Grid:

Now you can Sort the data in data preview pane. you can create the calculated fields, Groups and bins also.


Join:


When you pivot data, Tableau creates new columns. Now you can join on those columns and 
use these fields for new data integration and analysis.

Data Connections:

Salesforce.com OAuth Support:

Use the familiar Salesforce.com sign-in experience to sign in to Salesforce.com from Tableau Desktop, Tableau Online and Tableau Server.

Initial SQL Support:

Tableau now passes initial SQL commands when connecting to Oracle, Pivotal Greenplum, and Microsoft SQL Server to set session attributes.

Tableau Parameters in Initial SQL:

You can now include parameters in an initial SQL statement for a data source. This enables new scenarios, like row-level security, where users can only see the data they are authorized to see.

Kerberos for Teradata and PostgreSQL:

Leverage your IT investments in enterprise data security to create a seamless single sign-on flow from Tableau all the way to your database.

Performance:

On-Demand Connections:

See your data faster. When viewing a published workbook, Tableau connects only to the data sources that are required to display your view instead of connecting to all data sources in the workbook.

External Query Cache:

Workbook load times and interactions are faster thanks to caching. When using Tableau Data Server, the external query cache and in-memory query caches are better synchronized.

Maps:

Tableau added postal codes for 39 European countries, districts in India, and US demographic data layers for 2016. We’ve also updated postal codes for UK, France, Germany, and the US.


Forecasting:

Tableau automatically selects the best season length to help you forecast odd seasonal patterns and irregular time-series. You can also forecast values ordered by an integer dimension.


Excluding Totals for Color Encoding:

Separate your totals, subtotals and grand totals by excluding them from color-encoding.



Look at above image. Before 9.3 version Tableau applying color to totals also. So Dark color always for total of that table. All actual values will look lighter.

But in 9.3 Tableau excluding totals from color encoding. So easily we can see the highest and lowest values with color indication.

Sheet Colors:

Make your worksheets easier to identify and group by adding color to the 
sheet tabs in the sheet-sorter and filmstrip views.



Progressive Dashboard Load:
Not having to wait for the entire dashboard to load means you can start analyzing your data sooner.

Publish data source:

It’s easier to publish, keep your data fresh, and stay connected 
with the new Publish Data Source flow.



Mobile Sign In:

Sign into Tableau Online on your mobile device and stay signed in.
Enjoy hassle free access, thanks to Automatic Sign In.



Always Connected:

Publish your Tableau dashboards faster. Tableau Desktop remembers your Tableau Online
or Tableau Server connection and signs you in to the last server you used.



Tableau Online Sync:

Sync as a Service:
The Online Sync Client can be configured to be “always on” and run continuously so that your data stays fresh even when you’re signed out of the sync computer.

Notifications:

Sync with confidence. The Tableau Online Sync Client notifies you if a data source needs additional information such as user credentials or an updated file path.

Easy Setup:

Schedule extract refreshes right from a web browser. You’re guided through the Tableau Online Sync Client setup when you publish a data source to Tableau Online.

Search:

Search now shows you the most popular workbooks and views first.



Content Analytics:

Find popular workbooks and views by seeing how many views they have received.



Versioning:

Changes didn’t work out like you planned? Don’t worry! Just download the 
old version and keep going with Workbook Revision History for Tableau Server.



Content Management:

Project leaders can now change owners, run refresh schedules, and move content.



Tableau Server Management:

Rest API Enhancements:

The REST API is underpinned by a completely new platform with significant performance and usability improvements for admins.

Postgres Connectivity Monitoring:

You can now check the underlying PostgreSQL database for corruption with a new tabadmin command.

Low Disk-Space Monitoring:

Don’t unexpectedly run out of space. Tableau Server now lets you specify disk-space thresholds that alert you when space runs low.

PostgreSQL Improvements:

Fail over from one repository to another much more quickly. No server restart required.

Upgraded My Certification title to Tableau Desktop 9 Qualified Associate From 8

In June 2015 I appeared for Tableau Desktop 8 Qualification Exam. I was Qualified in that Exam and got 88%.

Later Tableau release Certification for 9 version. Tableau certifications are version specific and there is no validity limitation for your certificate. Once you are certified that will have life long validity for that particular version.

Suppose i got title as Tableau Desktop 8 Qualified Associate. That will be forever. But if you want to Upgrade your title to 9 version then you have to write upgrade exam. you need to clear Tableau Desktop 9 Delta Exam to upgrade your title from 8 to 9.

Similar way for Tableau Server also has Delta Exam. For More details visit certification info in my blog. Click here to see details.

This time I cracked Tableau Desktop 9 Delta Exam with 100% Score. Now I have two certifications.

Tableau Desktop 8 Qualified Associate
Tableau Desktop 9 Qualified Associate

You can see my certifications Below:

Tableau Desktop 8 Qualified Associate




Tableau Desktop 9 Qualified Associate



Exam Details:

Exam Duration: 45 mins
Knowledge Based Questions: 7
Hands on Questions: 5
Passing score: 75%

Based on above information I started my analysis. So total 22 marks you have to get 17 to clear this exam.

You have choice of loosing 5 marks. Suppose if you made 2 questions wrong in Hands on session you are gone. You will not qualify.

You have choice of getting wrong 1 hands on Question and 2 knowledge based questions only. This analysis help me to prepare well.

Tableau String Functions Assignment for Beginners

Here I am coming with assignment on Tableau string functions. This is very basic and fundamental assignment for beginners. Almost every body know functions in tableau. Lets see how they learned and understood.


In number functions assignment people are solving questions using string functions also. But these assignments are specific to category of functions. This assignment only on string functions.

1. How to count number of words in a sentence?

Example: 'Count the number of words in this sentence'

No of words in above sentence is: 8

2. How to write a calculation to reverse the words?

Example: "Suresh Nallapareddy" to "Nallapareddy Suresh"

3. How to count number of spaces in a string?

Example: 'Count the number of spaces in this sentence'

No of spaces in above sentence is: 7

4. How to convert "suresh reddy nallapareddy" to "Suresh Reddy Nallapareddy" (First letter of word should be capital)?

5. How to find first word in the string?

Example: 'find the first word in this string'

First word in the above string is: 'find'

6. How to find last word in the string?
Example: 'find the last word in this string'

Last word in the above string is: 'string'

7. How to find Nth word in a given string?

Example: 'find the 4th word in this string'

4th word in the above string is: 'word'

8. which function and how you will use to show "Suresh_Reddy_Nallapareddy" as "Suresh Reddy Nallapareddy"?

9. I have data like this

Name

Suresh*****
Ramesh***
Rakesh****

I want to show like this:

Name
Suresh
Ramesh
Rakesh

Single function can do this. What is that function and how you will write calculation for this?

10. How to calculate number of characters in a string after excluding spaces?

Example: 'Count the number of characters in this string excluding spaces'

Number of Characters in above string excluding spaces: 53

11. How to split email into 3 parts. See below example.

Email

suresh.n2008@gmail.com
suresh.n2008@tableauexpert.co.in
suresh.n2008@tableau.info

I want to show output like below:

Username           Domain                 Extension
suresh.n2008       gmail                      com
suresh.n2008       tableauexpert          co.in
suresh.n2008       tableau                    info

12. I have string like this: "s u r e s h" but i want to show it as "suresh".
Which function will do this? can you write calculation for this?