Calculations Flashcards
T/F
Another word for aggregation is row-level calculation
False
They are two different calculations
T/F
Aggregation and row-level calculations can appear in the same calculated field.
False
What are 4 types of calculations?
row-level
aggregation
table calculations
LOD expressions
https://www.tableau.com/blog/understanding-how-tableau-calculation-types-work-together
What is the order of calculations for the 4 types of calculations?
- row-level
- aggregation
- table calculations
- LOD expressions
row-level & LOD
aggregation
table calculations
https://www.tableau.com/blog/understanding-how-tableau-calculation-types-work-together
What calculation type is each formula:
SUM([Revenue])
[Quantity] * [Price]
RUNNING_SUM(SUM([Revenue])
Aggregation
Row-Lvl
Table Calculation
Image 1
https://www.tableau.com/blog/understanding-how-tableau-calculation-types-work-together
Name the calculation type of each part of this formula:
RUNNING_SUM(SUM([[Quantity] * [Price]])
Image 2
https://www.tableau.com/blog/understanding-how-tableau-calculation-types-work-together
Are aggregations preformed on columns or rows?
Hint:
Agg. examples: SUM(), AVG()
Aggregations are performed on a set of rows (records), grouping them by dimensions specified in the view.
Will this formula create a calculation error?
[Distance]/([Activity Date])
Yes, you cannot divide a number by a date
Will this formula create a calculation error?
[Distance]/COUNTD([Activity Date])
Yes, you cannot mix aggregate and non-aggregate arguments
What does IIF() do? What’s the correct syntax?
IIF(<test>, <then>, <else>, [<unknown>])</unknown></else></then></test>
Unknown is for nulls
IIF ( [Sales] > 10, “OK”, “NOT OK”, “NA” )
What is the correct syntax for CASE()?
CASE <expression>
WHEN <value1> THEN <then1>
WHEN <value2> THEN <then2>
...
ELSE <>
END <></then2></value2></then1></value1></expression>
T/F
All IF() can be written as CASE()
False, but all CASE() can be written as IF
T/F
To join 2 words end-to-end (e.g. “bat” “man” into “batman”) we would use CONCATENARE()
FALSE
there is no CONCATENATE() in Tableau
you would just use +
T/F
LOD Expressions are Table Calculations
False. These are 2 seperate things.
Answer these questions:
Calculations > 3 > Table Calculations fact check
https://campus.datacamp.com/courses/calculations-in-tableau/table-calculations-and-parameters?ex=2
How is a Moving Average of 30 calulated?
Smooth out noisy data by calculating & plotting the avg of previous entries.
Each point is calculated based on the average of the previous 30 days
T/F
A chart presenting a moving avg of 30-days-sales will have less data points than a char with daily sales
True
With a moving average, we “smoothen” the graph by reducing the # of data pts thanks to averaging them to one number per window.
CAGR ypically compares the values over a period [longer/shorter] than one year horizon, indexing avg growth in n years to the 1st year.
longer
T/F
Moving calculations require RUNNING family of functions & may use WINDOW functions in a secondary calculation.
False,
moving calculations require “bucketing” time intervals into windows anchored to a point in time, so they rely heavily on the WINDOW family of functions.
Comparing results per discrete month across various years is a ____ comparison.
year-over-year
T/F
This is a year-over-year comparison chart
Image 3
True
____ preforms a running calculation (e.g. SUM), restarting each year.
Hint: It’s a Quick Table Calculation
Year-to-Date (YTD)
What does CAGR stand for?
Compound Annual Growth Rate
What is an example of a CAGR?
5 or 5%
5%
What does the CAGR mean in this chart?
Image 4
In 2021, we observed a 32% avg compound annual growth rate over the previous 3 years, mainly driven by the spike in the last year
T/F
CAGR cannot be negative
False
[……….] calculations are used to see long-term trends.
- CAGR
- Moving
- YTD
Moving
Used for smoothing fluctuations in data
Handy when there are a lot of data points and a regular calculation wouldn’t give much insight.
List all of the Quick Calculations
Hint: 11
5 groups
Running Total
Percent Total
Difference
Percent Difference
Rank
Percentile
Moving Average
YTD Total
Compound Growth Rate
Year Over Year Growth
YTD Growth
Create outline of an IF statement
If ___ THEN ___
ELSEIF ____ THEN ___
ELSE ___
END
Birth Year should be [dimension/measure]
dimension
Lat & Long should be [dimension/measure]
dimension
Bins are [discrete/continuous]
discrete
Time series are [discrete/continuous]
continuous
T/F
All CASE() can be written as IF()
T