Just One Check/Uncheck Changes it - Dashboard Actions -
Yeah! Ichiro hit 3000th hit in MLB !!!
Ichiro Suzuki - Wikipedia, the free encyclopedia
But actually nobody around me mentioned that.
In U.S. people does not pay attention to baseball unless they have stadium in thier city.
That's different in Japan. Everybody loves Ichiro and all the newspaper mentioned that.
I love him from his first year in Japan Professional League.
By the way,
I got a question about unexpected Dashboard Actions behavior .
His dashboard was like this
Three worksheet were consolidated in a dashboard.
Actually this KPI is very good from the perspective of having multiple KPIs in one table.
Somtimes to have multiple KPI in one view is littel bit troublesome. (I can post this topic later)
But in his sheet, that problem is solved by using "Countinous" value as KPI.
If this is Discrete, we can not assign multiple colors in respective KPIs.
His dashboard action as dedigned as Sheet "KPI" as filter.
and "Deital" and "Sales by Product" shows only filtered value.
It woked well like below.
The problem was that
When user click "Customer Name" which makes more sense considering the relationships,
Filter did not work....
It was strange for me because my sample did work with exact same design.
This was caused by "AGAIN", just one check/uncheck type of thinig.
The difference was below
When you uncheck "Run on single select only", you will allow users to select below type of selections.
You can select multiple KPIs with CTRL key.
This is really useful in some case, but not alwasy good when you consider the Dashboad View which requires big scroll.
So you can check this box of "Run on single select only" when you want to limit one cutomer at same time, BUT, to allow "Costomer Name" as filter, you MUST uncheck this box.
I have not relized "Select Customer" is sistematically recognized as "Multiple Slection".
Small thing and pretty understandable, but we spent couple of hours to understand the Dashboard behavior as a fact.
Learning, Learning, Learning...
Enjoy Tableau !!
Customized Gantt Chart "Individual + Summary"
Here in Mid West/North U.S., usually we can enjoy pretty fine weather in this season.
Blue Sky and NO clouds.
However, becasue of too dry air, wild fire often happens. Then depend on the wind direction, smoke comes around the area and it smells. Hope the firefighters put out the fire soon and smoke goes away.
I got a good solution for the issue on title.
I had a gantt chart like this.
and at the same time, i want to show how many "categories" are "ON" at specific time slot.
Like this.
I can combine these charts on dashboard, but it's quite troulesome from the axis alignment persptctive.
The target is that how to combine individual Gantt chart and summary line /bar chart together in one sheet or completely aligned dashboard.
The data set:
Original data looks like below.
This is appropriate format to create Gantt chart with below logic.
[End Time - Start Time]
[End Time]-[Start Time]
However, we're in trouble when we try to create Bar chart to count items of "ON" status.
The reason is below.
To create Bar Chart with continous filed, we need to define one axis.
That should be either "Start Time" or "End Time".
In eitehr case, the mimimum "Start Time" is earlier than minimum "End Time" and maximum "End Time" is later than maximum of "Strat Time" from the given fact.
So in any case, the first plot or the last point must be missed.
What is needed first?
That's data pivot.
Now we got these data structure as a starting point.
Now Create Gantt Chart Again
This data strucure is much easier to handle than original GANTT chart.
However, we lost the relatioinships for same event's Start time and End time.
But as longs as we have original data source, we can make relationships between two source with linking "Category" and "Start time".
And we can get end time from original data source.
But we can be more creative with new data set.
We can create Gantt Chart-like chart
Create calculated field like below.
[Data for new Chart]
if [Pivot field names]="Start Time" then 1
elseif [Pivot field names]="End Time" then -1
else 0 end
Then add table calc of "Running_sum"
Add category as color.
And Here is an easy Trick!
"Show Missing Value" on Column.
==>
Here is a "GANTT"-like Bar Chart.
Then, it's easy to add Column Total on the bottom.
Others are just small modifications.
This is the last picture of combined chart.
Do you like that?
Enjoy Tableau !!
Who purchased both Product A and Product B
I got smilar questions couple of times regarding the issue I stated as title.
The below link is a case "Akiyah" tried to create VIZ with 4 calculated fields with using LOD calculations.
But as he staetd in the contents, it's little bit troublesome to create 4 fields.
So, I will propose different approach.
First of all, create new calculated field to categorize customers.
Create Calculated Field
[Customer Category]
if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0
and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2]elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "2-Purchased "+[Param 1]elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "3-Purchased "+[Param 2]ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2]
END
Little bit long calculation?? That's right. I add some explanations.
[Customer Category]
if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0 // in same month, same customer, sum up Param 1's sales and judge it is gerater than 0and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0 // in same month, same customer, sum up Param 2's sales and judge it is gerater than 0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2] // if both of above two conditions are met, the text shows up.
elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)} // in same month, same customer, sum up Param 1's sales
=
{fixed[Customer Name],[Order month] :sum([Sales])} // in same month, same customer, sum up ALL sales
then "2-Purchased "+[Param 1] // in case above two matches each other, the text shows up. that means only showing sales when only Param-1 are purchased.elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)} // in same month, same customer, sum up Param 2's sales
=
{fixed[Customer Name],[Order month] :sum([Sales])} // in same month, same customer, sum up ALL sales
then "3-Purchased "+[Param 2] // in case above two matches each other, the text shows up. that means only showing sales when only Param-2 are purchased.ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2] // all of other cases than above
END
The reason I add 1 to 4 on top of text is forcing to sort the category as alphabetical sorting, kind of TIPS, bit essential.
We put this calculated field on color shelf and get the view. Plus put this field on Label as well and we are able to see the category without color legend.
The benefit of this approach is that we can show the category name directly rather than show label by parameter name.
Here is the VIZ.
Yet, we feel a little bit unsatisfied ????
Because we want to highlight the category of "1- purchased both", but the VIZ does not meet that needs well.
So, additional works needed.
1. Highlight by Color
Emphasize "1- purchased both" means less emphasize others, then we change the color littel but lighter.
Double click on the red color of the category of "1- purchased both".
Then pick darker color.
Please ignore the preference of color for now, anyway, getting better.
2. Highlight by Label
It's not bad, but we are always greed to Tableau and want to emphasize the label as well.
We want to make one category's lable size larger.
This is not doen thru standard function and need some trick.
To make that happen, we need to create two more calculated fields only for this purpose. In this case, the formula should be very easy.
[HighLight]
if [Customer Category] = "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end
[Non-HighLight]
if [Customer Category] <> "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end
Put both fields on Label shelf
and open detail setting window.
Remove break line, select only <HighLight>, and change the format like below.
We can change the format of respective field when we put multiple fields in same label.
Plus, in the formula, we don't put else statement which automatically set "null".
The "null" does not show up anywhere and does not show up as "blank" neither.
Then, even in the case that we put multiple fields in same field, the label is not duplicated, provided the condition restricts to show only one value in one of these fields.
This is the final VIZ.
OK, Let's play around and enjoy!!
Different color for multiple categories on bar charts
Hello,
I want to put two categories' color into one bar cahrt.
I mean taht I want to create VIZ like below.
Actually there is very eay way to make this happen.
We can drag two dimensions at same time into color shelf.
With that, Tableau automatically select appropriate color setting for all combination of colors.
Done !
This is very easy and simple, however, there are some problems.
- Color legend shows 9 colors which is busy.
- When you try to change color feature, you need to change all 9 colors respectively.
- We lose the color we use for existing category. (<== Thsi is the bad side effect.)
For examle, I have different color set for "Product Category", but this work overwrite whole color settings.
So, I will show different method to achieve same result.
Let's start from simple VIZ like below.
I cahnged color set of "Product Category" intentionally.
We already have "Sales" field in the row shelf, but add one more "Sales" field into secondary axis.
Don't forget "Synclonize"
We can see two Bar charts settings.
Add "Ship Mode" into secondary axis's Color shelf.
Got below VIZ.
New Color on secondary axis overwrote exisitng primary axis's color.
Select "Edit Colors" on sencondary Axis Bar chart.
Gray 5 might be appropriate.
This the light ==> dark order is not appropriate, so we manually change "Delivery Truck" color.
Then on secondary bar cahrt's color shelf, we change the transparency.
25%'s case is as example, but you can choose the rate as you like.
Hide secondary axis's header.
And add label.
We could show "Ship Mode" category by thickness of color with keeping customized "Priduct Category" color.
OK, let's Play around and Enjoy!
Show Stacked Bar and Standard Bar Chart side by side
Today's Topic is showing differnt type of Bar chart side by side.
Actullay it's easy enough, but I got some questions from my collegues.
I used Tableau's sample data from the site.
Sample - Superstore Sales (Excel).xls |Tableau Support Community
Let's start from this type of view.
What we need to do are
- Group Low height items
- Show Stackd Bar cahrt only for Grouped Items.
Starting from sort by decending order.
We got below VIZ.
Group the items whose sales are below $500K.
There are multiple methods to do this, but I use LOD formula in this case.
[Province 2]
if {fixed [Province]:sum([Sales])} > 500000 then [Province] else "Small Sales" END
The function of "Fixed + Sum" is similar to EXCEL's "SUMIF" or "SUMIFS"
{fixed [Province]:sum([Sales])} means that nevertheless of other fields value, sum up with field of "Province".
Let's put [Province 2] on Column shelf instead of [Province].
We got below VIZ.
Becasue we chaged dimension, need to re-sort on [Province2]
When we put [Province 2] in color shelf, it looks below VIZ.
In Tableau's settnig, Items in column and Items in Label or Color etc are not neccessarily matched easy other.
So, we put [Province] instead of [Province 2] in color shelf.
Now we can see only [Small Sales] has Stacked Bar with different color.
Actually, the chart type stays same as "Standard Bar Chart", but with using different dimension as Color, you can show the standard bar chart like stacked.
Rest of small things.
Let's sort color legnend's order.
And this is completed VIZ.
OK, let's Play around and Enjoy!
What?? Why Cannot Synchronize Dual Axis
Keep talking about Dual Axis Chart
We want to create the cahrt to compare compnay to company.
At first, create the calculated field for comp,any A only sales.
[Sales (=Company A)]
sum(if[Company]="A" then [Sales] end)
To create Company B's field just after "A", using duplicate function helps you.
Now we have Compnay A's field and Compny B's field respectively.
Now, let's put Company A's field into primary axis and Company B's field into secondary axis.
Next, cahnge company B's field to Line chart.
Then, synclonize seconsary axis. simply use "Edit Axis".
Replace Label and change the text format.
I made it !!
Then Next, Let's compare Company A and other company's average.
Start from creating other Company's averatge field.
[Sales (<>Company A)]
sum(if[Company]<>"A" then [Sales] end)/2
We put this field on secondary axis with overwritng existing field.
We sunclonize secondary Axis.
What ???!!!
.
The check box is grayed out!.
Shall I us "Fixed" value ?? No No, it's not elegant.
I could do it a few minutes ago and why suddenly cannot?
What's the difference??
Yes, it's dicimal
Tableau's each field has own "category" like date , text or number etc.
The numebr has two cagegories of Integer and Float.
If the field has dicimal, it's recognized as "Float".
And we cannot synclonize Integer Fields and Float Field.
This is not related to how the fields looks, but related to the content itself.
I mean we need to change the formula itself to align the category.
[Sales (<>Company A)]
int(sum(if[Company]<>"A" then [Sales] end)/2)
Now let's try it again.
Great, it's automatically synclonized and check box is also editable.
As a take away, when you have trouble to synclonize dula axis, you may have different category field. After cahnging the category of field, you can synclonize the dual axis.
OK, Let's play aroudn and Enjoy!
This not what I want : Dual Axis Graph's synchronization
I have not updated several days, let's start.
We want to draw dual axis chart with abar and Graph.
It's not difficult at all, but couple of TIPS.
Starting from this graph.
We want to show the delta percentage from last month.
We use table aclculationi, it's easy enough.
You drag the table calclation Pill in Rows, then the below graph is available, but not good enough.
We want to show it as line chart.
You can see dotted line when you drag "Difference percentage" pill to right-end of bar chart, which means you can use this field as 2ndary axis.
It's overwrapped, but bar graph is not good to see.
let's change the type to Line from Bar.
It's done, but somethign is not good.
The reason is Bar chart's main Y-axis include negative numbers.
Only Line chart should have negative number, in this case.
You can change Mian Y-Axis "mimium" as "Zero", but that methos is not "Elegant" at all.
Here is an "Elegant" solution.
On 2nd axis, un-check "Include zero".
Then ,
really done.
We still need to modify lavel . with CTRL, drag "Difference percentage" pill to Line chart's label.
Some lable is overwrapped, but ignorable as long as you show this through Tableau.
If you need to copy paste to PowerPoint, minor making up will be needed, though.
ON more final causion:
If you use Main axis to "exclude zero", the graph lokk like below.
It seems working, but this graph is not at all correct
Becasue bar graph does not include zero, it starts from 12800,
This is the first thing we need avoid when we create bar cahrt, please pay attention.
K, Let's play aroudn and Enjoy!