Skip navigation

teste2 new.jpg

How to compare a Region sales to the overall sales?

 

Situation:  You want to have a line chart which you can choose one Region and compare to the sum of all regions, across year and customer segment.

How do you achieve the desired result?

 

I guess there are a lot of ways to do that, but today we are going to use the duplicate datasource approach.

In the follow example, we are going to use Superstore data set.

So, lets start.

 

Step 1 - Duplicate your data source

1 Duplicate.png

 

Step 2 - Rename the duplicated datasource (optional)

2 Rename.png

 

Step 3 - Edit relationships

In our case, we want to see a line per customer segment across the year, filtering a region (on the primary datasource) and comparing to overall regions.

So, we will blend by Order Date and Customer Segment.

4 Edit Relatioships.png

 

Step 4 - Create your viz

We are almost done. Now we will create our viz.

From SuperStore datasource:

4.a Drag&Drop "Customer Segment" to Colors

4.b Drag&Drop Order Date to Columns

4.c Drag&Drop Sales to Rows

 

You will have something like this:

5 Viz.png

 

Step 6 - Include the overall Sales

Now we will include the overall Sales per Customer Segment per Year.

From the Overall datasource:

6.a Make sure the links are in use

6.b Drag&Drop Sales to Rows

6 Finish.png

 

Step 7 - Finish

7.a Change the secondary Sales to Dual Axis and then Synchronize the Axis.

7 Duas Axis.png

7.b Change the size of the secondary Sales

7 Size.png

7.c Add transparency to the secondary Sales

7 Color.png

7.d remove "Measuare Names" from Color.

7 Remove Measure Names.png

 

Now you can filter any field from the primary datasource and compare to the overall.

http://public.tableausoftware.com/static/images/Di/DimensionXOverall/Compare/1.png

 

If you are using Tableau 9, there is a much simpler way to achieve that result, by using LOD. Visit OVER (PARTITION BY)

 

Also, visit my personal blog Data Visualization & Preparation tips.

Americo Guazzelli

DATEDIFF Semester

Posted by Americo Guazzelli Mar 3, 2015

Sheet 17 new (1).jpg

How to calculate the semester difference between 2 dates

In other words, how to simulate the date_part 'semester' in the DATEDIFF() function.

 

There are many ways to reach that.

In this blog, I'll explain 2 of them.

 

# 1 - Using DATEDIFF

 

All you need to to is :

     +  use the DATEDIFF function to get the quarter difference between the two dates

     + Divide the result by 2

     + get the int part of the division.

 

Exemple:

INT(DATEDIFF('quarter',start date,end date)/2)

 

# 2 - Using date calculation (for the SQL maniacs...)

You will need the year and month of both dates:

 

 

(YEAR(end date) - year(start date) ) *2

+

(

     (IF MONTH(end date) >= 7 THEN 2 ELSE 1 END)

     -

     (IF MONTH(start date) >= 7 THEN 2 ELSE 1 END)

)


Visit my personal blog Data Visualization & Preparation tips.