Plan and Implement Calculations in Tableau Desktop - 38% Flashcards
what is fixed LOD and when to use
This expression computes values using the specified dimensions without reference to any other dimensions in the view.
use when: aggregates the value only at the dimensions which are specified by the user in the calculation
what is include LOD and when to use
This level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
use when a user wants to calculate values at the lower level of granularity and then aggregate and show the values at the higher level of granularity
what is exclude LOD and when to use
These levels of detail expressions subtract dimensions from the view level of detail.
use when you want to omit specified dimensions from the aggregations
although in the view we are going to show only “Segment” in calculations we need to include “Customer Name” as well so that average customer sales can be calculated.
what are LOD expressions
used to run complex queries involving many dimensions at the data source level instead of bringing all the data to Tableau interface
example of INCLUDE
compare the average customer sales across different product segments
example of EXCLUDE
compare total sales with the monthly sales of the East region
To get the monthly sales we need to have “Region”, “Month” and “Sum of Sales” in the view. But, now to get the total sales of Region, we need to exclude “Month” in the calculation so that we can have total sales value for the region.
example of FIXED
calculates the sales across regions
even if a user puts another field say “State” in the view, the fixed calculation still calculates the value at region level only
ABS
returns the absolute value of the specified number.
Example: ABS(-10) = 10
CEILING
rounds the given number to the nearest integer of equal or greater value.
Example: CEILING(5.1265) = 6
FLOOR
rounds the given number to the nearest integer of equal or lesser value
Example: FLOOR(5.1265) = 5
LOG
returns the logarithm value of the number for the given base. If the base value is omitted, base 10 is being used by default
Example: LOG(100) = 2, LOG(256,2) = 8
MIN
Returns the minimum value of an expression across all records
Example: MIN([Profit])
This expression will return the minimum value of the profit across all the profit values
MAX
Returns the maximum value of an expression across all records
Example: MAX([Profit])
This expression will return the maximum value of the profit across all the profit values
POWER
It returns the result of a number raised to the given power
Example: POWER(5,2) = 25
SQRT
It returns the square root of a given number
Example: SQRT(25) = 5
ZN
“ZN” stands for “Zero if Null”. It means it returns the given expression if it is not null, otherwise returns zero.
CONTAINS
returns “True” if the user’s mentioned sub-string is present in the given string.
Example: CONTAINS(“University”, “versi”) = True
ENDSWITH
It returns “True” if the given string ends with the user’s mentioned sub-string
Example: ENDSWITH(“University”, “sity”) = True
FIND
returns the position of the mentioned sub-string within the given string. If the sub-string isn’t found then it returns 0(zero)
Example: FIND(“University”, “versi”) = 4
ISDATE
used to check whether the given string is a valid date. If the string is valid date then it returns “True”
Example: ISDATE(“2017-05-12”) = True
LEFT
returns the specified number of characters from the start of the string
Example: LEFT(“Calculation”, 4) = Calc
LEN
returns the number of characters in the given string.
Example: LEN(“Calculation”) = 11
LOWER
Convert a text string to all lowercase letters
Example: LOWER(“CalCulation”) = calculation
MID
It returns the characters from the middle of a text string given a starting position and length.
Example: MID(“Tableau”,2,4) = able
REPLACE
returns a string in which every occurrence of the substring is replaced with the replacement string.
Example: REPLACE(“Calculation”, “ion”, “ed”) = Calculated
RIGHT
returns the specified number of characters from the end of the given string
Example: RIGHT(“Calculation”, 4) = tion
TRIM
Returns the string with both trailing and leading spaces removed
Example: TRIM(“ Budget “) = Budget
UPPER
Convert a text string to all uppercase letters
Example: LOWER(“Budget”) = BUDGET
DATEADD
Add an increment to the specified date and returns the new date.
Example: DATEADD(‘month’, 3, #2017-07-12#) = 2017-10-12 12:00:00 AM
DATEDIFF
Returns the difference the two dates where start_date is subtracted from end_date
Example: DATEDIFF(‘month’, #2017-04-12#, #2017-07-15#) = 3
DATENAME
Returns the part of the given date as string.
Example: DATENAME(‘month’, #2017-05-14#) = May
DATEPARSE
It converts a string to a date in the specified format
Example: DATEPARSE(“dd.mmmm.yyyy”, “15.April.2017”) = 2017-04-15 12:00:00 AM
DATEPART
Returns a part of the given date as an integer where the part is defined by date_part.
Example: DATEPART(‘month’, #2018-01-17#) = 1
MONTH
Returns the month of the given date as an integer
Example: MONTH(#2018-02-19#) = 2
TODAY
Returns the current date
YEAR
Returns the year of the given date as an integer
Example: YEAR(#2018-02-19#) = 2018
DATE
Returns a date given a number, string or date expression
Example: DATE(“2011-03-12”) = 2011-03-12
FLOAT
It returns the floating number from the given expression of any type
Example: FLOAT(“3”) = 3.00
INT
Returns an integer given an expression
Example: INT(-9.7) = -9
STR
Returns a string given an expression
Example: STR(90) = “90”
AND
This function performs the logical conjunction of two or more expression. “AND” returns “True” when all the given expressions are true.
CASE
This function finds the first that matches the given and returns the corresponding
Example:
CASE [Name] WHEN “John” THEN 90
WHEN “Emma” THEN 95
END
IFELSE
It tests a series of expressions and returns only that value corresponding to the first expression is true
Example: IF [Profit] >0 THEN “Profitable” ELSEIF [Profit] = 0 THEN “Breakeven” ELSE “Loss” END
OR
This function performs the logical disjunction on two or more expression. “OR” returns “True” when any of the given expressions are true.
ATTR
It returns the value of the given expression if it only has a single value for all the row in the group., otherwise it displays an asterisk (*). NULL values are ignored by this function
AVG
It returns the average value of the given expression or array of values
COUNT
It returns the count of items in the group. NULL values are not counted
COUNTD
It returns the unique count of items in the group.
MEDIAN
Returns the median value of an expression across all records
Example: MEDIAN([Profit])
This expression will return the median value of the profit across all the profit values
PERCENTILE
an aggregate calculation that returns the percentile value from the given expression, corresponding to the specified percentile value
Example: PERCENTILE([Sales],0.9) = This expression will return the 90th percentile value from all the Sales value
SUM
returns the sum of the given expression or array of values
SPLIT
separate values based on a separator or repeated pattern - like first and last names
LOOKUP
returns the value of the expression in a target row, specified as a relative offset from the current row.
LOOKUP(expression, [offset])
Example: LOOKUP(sum([employee count]),-1)
this calc finds the difference in number of employees hired from previous year (row)
TOTAL
returns the total for the given expression in a table calculation partition. Use this calculation for totals.
Example: TOTAL(SUM([Employee Count]))
this gives total number of employees hired each year
RANK
returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional ‘asc’ | ‘desc’ argument to specify ascending or descending order. The default is descending.
RANK(expression, [‘asc’|’desc’])
WINDOW_AVG
WINDOW_AVG(expression, [start, end])
returns the average of the expression within the window. The window is defined by means of offsets from the current row.
INDEX
returns the index of the current row in the partition, without any sorting with regard to value
FIRST
returns the number of rows from the current row to the first row in the partition
example: LOOKUP(ZN(SUM([Employee Count])), FIRST())
The first part of the calculation finds the value for the current row, and the second finds the value for the first row.
LAST
returns the number of rows from the current row to the last row in the partition.
EXAMPLE: LOOKUP(ZN(SUM([Employee Count])), LAST())-LOOKUP(ZN(SUM([Employee Count])), FIRST()).
The first part of the calculation finds the value for the last row, and the second part finds the value for the first row.
WINDOW_MAX
Returns the maximum of the expression within the window. The window is defined by means of offsets from the current row
Example
WINDOW_MAX(SUM([Profit]), FIRST()+1, 0) computes the maximum of SUM(Profit) from the second row to the current row
PREVIOUS_VALUE
Returns the value of this calculation in the previous row. Returns the given expression if the current row is the first row of the partition.
Example
SUM([Profit]) * PREVIOUS_VALUE(1) computes the running product of SUM(Profit)
RANK_DENSE
Returns the dense rank for the current row in the partition. Identical values are assigned an identical rank, but no gaps are inserted into the number sequence.
RANK_MODIFIED
Returns the modified competition rank for the current row in the partition. Identical values are assigned an identical rank
RANK_PERCENTILE
Returns the percentile rank for the current row in the partition.
RANK_UNIQUE
Returns the unique rank for the current row in the partition. Identical values are assigned different ranks.
RUNNING_AVG
Returns the running average of the given expression, from the first row in the partition to the current row.
RUNNING_AVG(SUM([Profit])) computes the running average of SUM(Profit).
RUNING_COUNT
Returns the running count of the given expression, from the first row in the partition to the current row.
Example
RUNNING_COUNT(SUM([Profit])) computes the running count of SUM(Profit)
RUNNING_MAX
Returns the running maximum of the given expression, from the first row in the partition to the current row.
Example
RUNNING_MAX(SUM([Profit])) computes the running maximum of SUM(Profit)
RUNNING_MIN
Returns the running minimum of the given expression, from the first row in the partition to the current row.
RUNNING_MIN(SUM([Profit])) computes the running minimum of SUM(Profit).
RUNNING_SUM
Returns the running sum of the given expression, from the first row in the partition to the current row.
Returns the running sum of the given expression, from the first row in the partition to the current row.
SIZE
Returns the number of rows in the partition.
WINDOW_SUM
Returns the sum of the expression within the window. The window is defined by means of offsets from the current row
calculate a weight for each category and then apply that to the priority score.
Example
WINDOW_SUM(SUM([Profit]), FIRST()+1, 0) computes the sum of SUM(Profit) from the second row to the current row
difference from
computes the difference between the current value and another value in the table for each mark in the visualization
moving calculation
determines the value for a mark in the view by performing an aggregation (sum, average, minimum, or maximum) across a specified number of values before and/or after the current value
typically used to smooth short-term fluctuations in your data so that you can see long-term trends
percent difference from
computes the difference between the current value and another value in the table as a percentage for each mark in the visualization
percent from
computes a value as a percentage of some other value—typically, as a percentage of the previous value in the table—for each mark in the visualization.
Percent of Total calculation
computes a value as a percentage of all values in the current partition.
percentile
computes a percentile rank for each value in a partition.
running total calculation
aggregates values cumulatively in a partition.
