- Multidimensional data sources do not support aggregations and binned data.
- Multidimensional data sources supports only in windows.
- You can use tableau to aggregate measures only with relational data sources, multidimensional data sources contain aggregated data only.
- Null values are ignored in median, count, sum, avg and countD.
- Median does not work on live connection.
- Median was not there before 8.2 version.
- Percentile required extract data. Percentile works with only extracts.
- When all measures are disaggregated you see a mark for each row in the view, you can't select marks to keep only, exclude or create a set when all measures are disaggregated.
- A dimension can be aggregated as a measure using minimum, maximum, count and count (distinct)
- Count (Distinct) is not support for Microsoft Access, Microsoft Excel and text files data sources. These data sources requires extract to work with CountD
- Dis-aggregating the data can result in a performance degradation.
- When you are writing formulas, any part that displays in bold indicates that it will be computed locally with in tableau on the aggregated results. Any normal weight text will be computed at the database level.
- Functions are displayed in Blue color while creating calculation field.
- Data window fields are displayed in Orange color while creating calculation field.
- Operators are displayed as Blank color while creating calculation field.
- Parameters are displayed as Purple color while creating calculation field.
- Comments are colored as Green color while creating calculation field.
- Grand Totals cannot be applied to continuous dimensions.
- The view must have at least one header to apply Grand Totals.
- If row headers are displayed you can calculate Grand Total for row, If column headers are displayed you can calculate Grand Total for column.
- Forecasting is not supported for multidimensional data sources.
- Forecasting doesn’t work if the view contains:(a) Table calculations(b) Disaggregated measures(c) Percent calculations(d) Grand Totals or Sub Totals(e) Date values with aggregation set to exact date(f) A time series contains null values also imposes constraints.
- The smaller the p-value, the more significant the model.
- If your data contains negative values tableau cannot plot them on a logarithmic scale. All values with a negative value will be displayed at 1 on the axis.
- The pattern match is not case sensitive in filters wild card match.
- If we are using multidimensional data source wild card match option is only available when filtering single level hierarchies and attributes.
- Filters cannot be applied across multiple data sources.
- In previous versions of tableau desktop, the All using this data source option was called make global and the only this worksheet option was make local.
- Sorted fields are identified by a sort icon on the right side of the field.
- You cannot nest inner joins within left or right joins. These joins will cause a "join expression not supported" error.
Important notes in Tableau Desktop
Scenario 4 (Applying filters only for numerator, not for denominator using FIXED( ) in Tableau 9)
Scenario
4:
Problem:
Applying filters only for numerator, not for denominator using FIXED
function in
Tableau 9
Description:
Lets take sample super
store data set. I want to see count distinct of “Order
Id” Group by “Customer Segment”
and “Region” as my Denominator. Here
is the tricky thing that I have two filters on filter shelf
“Container” and “Department”
But when I am calculating count distinct of “Order
Id” only one filter should apply to Denominator that is
“Container” See below:
Before applying
filters:
See above if I did not
apply filter then it showing Denominator and nominator both are same
values.
Now I am changing
“Container” filter. The filter will
apply to both Denominator and Numerator.
Now I am applying
“Department” filter. Now Only
numerator will change. The “Department”
filter wont apply to Denominator. See Below:
How
we can restrict filters on denominator?
Solution:
Step
1: Create a calculate field for “Numerator” using below formula.
Countd([Order
ID])
Step
2: Create a calculate field for “Denominator” using below
formaula.
{FIXED
[Customer
Segment],
[Region]:countd([Order
ID])}
Step
3: Drag columns to respective shelves like shown below, make
Container
filter as context filter.
- Drag “Customer Segment” and “Region” to row shelf
- Drag “Container” and “Department” to filters shelf
- Right click on “Container” column in filter shelf and click on “Add to Context”
- Drag “Numerator” and “Denominator” to text shelf
- Go to Show me and select text table
Now
you can change filters and observe that “Container”
filter is applying only for Denominator.
You
can see that “Container”
and “Department”
filters are applying to numerator.
See the report below:
See the report below:
Scenario 3 ( Removing Subtotals for unwanted columns)
Scenario
3: (Simple)
Problem:
When we add subtotals by default it will add subtotals for each
dimension in cross tab report.
Description:
Lets take sample
superstore dataset. I made one cross tab report with following
columns in same order.
Region,
Category, Department, Container, Consumer Segment and Sales.
After adding subtotals
report look like this:
In above report I don't
want totals for Category and Region.
How
to remove subtotals for unwanted columns?
Solution:
Step
1: Build
cross tab report using following columns.
Drag
Region,
Category, Department, Container, Consumer Segment Columns
to row shelf. Drag sales
column to text Self.
Step
2: Add
Subtotals to report. Go to Analysis
tab, select Totals
and click on Add All Subtotals
Now
the report looks like this:
Step
3:
Right click on Region
column which is there in row shelf to remove subtotals. By default it
is selected, Click on Subtotals
to
remove subtotals.
Step
4:
Repeat step 3 for
Category
column to remove subtotal.
After
removing subtotals for Region
and Category
the report looks like this:
See the report below:
Scenario 2 (Gap Analysis using floating Bar chart)
Scenario
2: (Complex)
Problem:
Gap
analysis between demand vs supply with floating gap bar along with
conditional formatting. See below image.
Gap =
Supply - Demand
Description:
- The gap that is Supply – Demand should display in between supply and demand bars.
- If the gap is -ve mean supply is less than demand then gap bar should be in red color or if the gap is +ve means supply is more than demand then gap bar should be in green color.
- In the same way gap bar should start from end of demand bar if it is less than supply or it should start from end of supply bar if is less than demand.
Questions
that I raised:
- How to display Gap bar between Demand and Supply in floating mode.
- How to make Gap bar start from end of either Demand or Supply (start point depends upon which one is low among Demand and Supply)
- How to do conditional formatting for floating bar.
Solution:
please
follow step by step clearly
This
report build with sample data that I prepared please see the sample
data
Step
1: In
present dataset we don’t have gap so we need to calculate gap using
below formula. Create a calculated field “Supply - Demand (Gap)”
[Supply]
- [Demand]
Step
2:
Create a calculated field for conditional formatting as “+ve or -ve
Gap” (solution of my 3rd
Question)
if
[Supply
- Demand (Gap)] <
0 then "-ve Gap" else "+ve Gap" END
Step
3:
Drag year column to filter and select 2014. Drag Quarter column to
column shelf , Drag Demand , Supply columns to row shelf. Go to Show
me and select side by side bar. You will see like this
Step
4:
Create a calculated field as “Gap (ABS)” to get the Absolute
value of Gap.
float(abs([Supply
- Demand (Gap)]))
Step
5:
Create a calculated field “Gap” to make the starting point of
floating bar. This will depend upon Demand and Supply values. Which
is low among these two will become start point for floating bar.
if
sum([Supply])
> sum([Demand])
then float(sum([Demand]))
else float(sum([Supply]))
END
Step
6:
Drag “Gap” column that created in step 5 to rows shelf, beside
“Measure Values”. See below
Step
7: Right click on “Gap” axis and select dual axis. If is not
showing bar chart then go to marks shelf and select bar. It should
like this
Step
8:
Right click on “Gap” axis which on right side and select
“Synchronize Axis”, again right click and click on Show Header.
Then the right side Axis will go hide.
Step
9:
Go to Marks shelf, select “Gantt Bar” in Gap marks shelf drop
down.
Step
10:
Drag “Gap (ABS)” field which is created in step 4 to size shelf.
Now you will see floating gap bars in chart.
Step
11:
Drag conditional formatting calculated field “+ve or -ve Gap”
which is created in step 2 to Gap marks shelf.
After
adding conditional formatting field to Gap marks shelf report will
looks like this:
Step
12:
Now we have to show labels to bars. Just click on “Abc” from tool
bar and format labels to standard format.
Step
13:
See the label for gap floating bar it showing either Demand or Supply
values because we use them for starting point of float bar. So we
need to change this label. Go to label shelf of Gap marks shelf,
unchecked show mark labels.
Step
14:
Now Drag “Supply - Demand (Gap)” field which created in step 1 to
label shelf of Gap Marks Shelf. Finally report is completed.
See the report below:
Scenario 1 (Mapping 5 parameters to top 5 Quarters)
Scenario
1 (Medium)
Problem:
we need to map each parameters to corresponding quarter to
calculate estimated uplift sales.
Description:
Lets take sample
superstore subset data set. I am more interested to see top 5
quarters of sales in my data set. I plot a line graph like below:
13Q4, 13Q3, 13Q2, 13Q1
and 12Q4 are my latest quarters in present dataset.
Note: assume
that 13Q4 as my current quarter and follows other as
13Q3 as LQ1
13Q2 as LQ2
13Q1 as LQ3
12Q4 as LQ4
Now I want to show one
more line with estimated % of increase in sales. I will pass
estimated % values for each Quarter using parameters.
My Parameters list:
Estimated % for
CQ
Estimated % for
LQ1
Estimated % for
LQ2
Estimated % for
LQ3
Estimated % for
LQ4
Now I need to calculate
Estimated sales for each quarter using following formula
if attr(quarter)
= '13Q4' then sum(sales)
+ (sum(sales)
* (Estimated % for CQ/100))
elseif attr(quarter)
= '13Q3' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ1/100))
elseif attr(quarter)
= '13Q2' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ2/100))
elseif attr(quarter)
= '13Q1' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ3/100))
elseif attr(quarter)
= '12Q1' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ4/100))
END
The above formula
should run for each row in the data set. But we cant hard code these
quarters like 13Q4, 13Q3 …...12Q1 because these data will come
dynamically from data source. In future you may see 14Q4,
14Q3....13Q1
Here
is the problem how to know which quarter is coming in current row? I
mean is CQ or LQ1 or LQ2 or LQ3 or LQ4?
Solution:
After
Knowing the solution you might feel simple. But this logic I got
after several trials.
First
create a 5 parameters with following names
Estimated % for
CQ
Estimated % for
LQ1
Estimated % for
LQ2
Estimated % for
LQ3
Estimated % for
LQ4
Just remember once
again the calculate field for Estimated uplift sales:
if attr(quarter)
= '13Q4' then sum(sales)
+ (sum(sales)
* (Estimated % for CQ/100))
elseif attr(quarter)
= '13Q3' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ1/100))
elseif attr(quarter)
= '13Q2' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ2/100))
elseif attr(quarter)
= '13Q1' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ3/100))
elseif attr(quarter)
= '12Q1' then sum(sales)
+ (sum(sales)
* (Estimated % for LQ4/100))
END
In Above formula we
need to replace 13Q4,13Q3 …..12Q1 dynamically. Let think that if I
give positions for top 5 quarters as below
Top Quarter in data set
---> position as 1 and follows see below example
13Q4 ----> 1 and see
I can map this to CQ parameter
13Q3 ----> 2 this to
LQ1 parameter
13Q2 ----> 3 this to
LQ2 parameter
13Q1 ----> 4 this to
LQ3 parameter
12Q4 ----> 5 this to
LQ4 parameter
how to give
positions to Quarters?
Using rank function we
can give positions to quarters dynamically.
Create a calculate
field as “Rank Quarter” using below formula.
rank(attr([Quarter]))
now you will see like
this
this rank will update
for future quarters.
Now we will write
calculation field for Estimated uplift sales using Rank Quarter
field.
if [Rank
Quarter] = 1 then sum(Sales)
+ (sum(Sales)
* ([Estimated % for CQ]/100))
elseif [Rank
Quarter] = 2 then sum(Sales)
+ (sum(Sales)
* ([Estimated % for LQ1]/100))
elseif [Rank
Quarter] = 3 then sum(Sales)
+ (sum(Sales)
* ([Estimated % for LQ2]/100))
elseif [Rank
Quarter] = 4 then sum(Sales)
+ (sum(Sales)
* ([Estimated % for LQ3]/100))
elseif [Rank
Quarter] = 5 then sum(Sales)
+ (sum(Sales)
* ([Estimated % for LQ4]/100))
END
Now place the columns
in respective shelves and make a dual axis chart like below
Note:
I did not include two
things above.
1. calculation field to
extract “Quarter” column from “Order Date” column
right(str(year([Order
Date])),2) + 'Q' + str(datepart('quarter',[Order
Date]))
2. Applying top 5
filter on Quarter column to see top 5 quarters
See the report below:
