Sunday 1 November 2015

Writing Conditional calculations in Tableau

Conditional Calculations:
If any calculation you want to use in the report which is not there in database, then we create that calculation with right syntax at tableau level.
And we use the Calculations for customization purpose.
For An Example: Sales less than 10 K is  "Worst Sales", >10 K and < 15 K is "Average" , >15 K and < 20 K is "Above Average" , >20 K and < 50 K is "Meet Target" , >50 K and < 75 K is "Excellent Sales."

1) If condition with 1 Possibility  without Else part
Syntax: IF [Condition Matches] then [Matched Value]  end
EX: If  sum([Sales])>15000 then "Good"  end

Description: In the above example, If sales are more tan 15000 then those will be represented as "Good" (If condition matches then "Good" if Condition does not match then empty, i.e. null)
If Sales does not more then 15000 then those values are represented by empty since we did not define else part.
Next example will explains how OUTPUT will effect if we define else part.  


2) If condition with 2 Possibilities including Else part
Syntax:
IF [Condition Matches] then [Matched Value] else [Non Matched Value]  end
EX: If  sum([Sales])>15000 then "Good" else "Average" end
Description:
In the above example, If sales are more tan 15000 then those will be represented as "Good" (If condition matches then "Good" if Condition does not match then empty, i.e. null)
If Sales does not more then 15000 then those values are represented by  "Average" since we defined else part.


3) If then else - more than 2 Possibilities
if [Condition 1 Match] then [Value1]
elseif [Condition 2 Match] then [Value 2]
elseif [Condition 3 Match] then [Value 3]
elseif [Condition 4 Match] then [Value 4]
.....................
Else [Value N] end
EX:
If sum([Sales]) <15000 then "Below Average"
elseif  sum([Sales]) >=15000 and sum([Sales]) <25000 then "Average"
elseif  sum([Sales]) >=25000 and sum([Sales]) <50000 then "Above Average"
else "Excellent" end
=============================
Case <EXP>
when <Matches 1> then [Value 1]
when <Matches 2> then [Value 2]
when <Matches 3> then [Value 3]
Else [Value 4] end

Case [Region]
when "Central" then "Territory - 1"
when "East" then "Territory - 1"
else "Territory - 2" end

==================
1) I want to Apply Different colors to Regions as per Sales Value.
< 10 K - Color 1
> =10 K And < 15 K then Color 2
> =15 K And < 25 K then Color 3
or Color 4
2) Difference between And , Or Operators in the Calculations.
Create few reports with BUZ Requirement.
3) I want to see 2013, 2014 data in 1 color, 2011, 2015 in another color.
(Don't use Editing Color Legend option for applying colors. Use Calculations to define colors)
4) Categorize the City's into different groups by using your own business situation.
5) I want apply Different shapes as per Sales Values.
Use Question -1 as situation & define the shapes.
Note:
As of now Tableau does not have "IN" operator in the Calculation but there is an alternative that is "OR"
Case [Region]
when "Central" then "Territory - 1"
when "East" then "Territory - 1"
else "Territory - 2" end
Or
If  [Region] = "Central" or   [Region] ="East" then "Territory - 1"
else "Territory - 2" end

No comments:

Post a Comment