Calculations Flashcards by Alicia Alcaraz

T/F

Another word for aggregation is row-level calculation

False

They are two different calculations

How well did you know this?

Not at all

Perfectly

T/F

Aggregation and row-level calculations can appear in the same calculated field.

False

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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.

How well did you know this?

Not at all

Perfectly

Will this formula create a calculation error?

[Distance]/([Activity Date])

Yes, you cannot divide a number by a date

How well did you know this?

Not at all

Perfectly

Will this formula create a calculation error?

[Distance]/COUNTD([Activity Date])

Yes, you cannot mix aggregate and non-aggregate arguments

How well did you know this?

Not at all

Perfectly

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” )

How well did you know this?

Not at all

Perfectly

What is the correct syntax for CASE()?

CASE <expression>
WHEN <value1> THEN <then1>
WHEN <value2> THEN <then2>
...
ELSE <>
END <></then2></value2></then1></value1></expression>

How well did you know this?

Not at all

Perfectly

T/F

All IF() can be written as CASE()

False, but all CASE() can be written as IF

How well did you know this?

Not at all

Perfectly

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 +

How well did you know this?

Not at all

Perfectly

T/F

LOD Expressions are Table Calculations

False. These are 2 seperate things.

How well did you know this?

Not at all

Perfectly

Answer these questions:
Calculations > 3 > Table Calculations fact check

https://campus.datacamp.com/courses/calculations-in-tableau/table-calculations-and-parameters?ex=2

How well did you know this?

Not at all

Perfectly

How is a Moving Average of 30 calulated?

Smooth out noisy data by calculating & plotting the avg of previous entries.

e.g. Each point is calculated based on the average of the previous 30 days

How well did you know this?

Not at all

Perfectly

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.

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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.

How well did you know this?

Not at all

Perfectly

Comparing results per discrete month across various years is a ____ comparison.

year-over-year

How well did you know this?

Not at all

Perfectly

T/F

This is a year-over-year comparison chart

Image 3

True

How well did you know this?

Not at all

Perfectly

____ preforms a running calculation (e.g. SUM), restarting each year.

Hint: It’s a Quick Table Calculation

Year-to-Date (YTD)

How well did you know this?

Not at all

Perfectly

What does CAGR stand for?

Compound Annual Growth Rate

How well did you know this?

Not at all

Perfectly

What is an example of a CAGR?

5 or 5%

How well did you know this?

Not at all

Perfectly

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. 1. CAGR 2. Moving 3. YTD 4. YOY

1. **CAGR** - compound annual growth rate - compares multiple years 2. **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.) 3. NOT YTD - only analyzes 1 year 4. **YOY** - compare each year to see for monthly/seasonal trends (potentially good for more granular levels like day or weekday if filtered?) ## Footnote https://campus.datacamp.com/courses/calculations-in-tableau/time-series-analysis?ex=6

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()

Bins are created from a [**continuous/discrete**] [**dimension/measure**]

continuous measure

How do you create a bin?

1. Right click a continuous measure 2. "Create" 3. "Bins" 4. Edit the Bin in the "Edit Bin" popup

Bins are [**continuous/discrete**] fields by default

Discrete

_ bins show only the starting value of each bin * continuous measure * continuous dimensions * discrete measure * discrete dimensions | (Image 5)

discrete dimension | (bins cannot be measures)

You want your bins to look like **Image 6**, but they currently look like **Image 5**. How do you change this?

Change the field in columns from discrete to continuous.

How do you create a bin size parameter?

1. Create bin calculated field 2. In the "Edit Bin" popup, change "**Size of Bin**" to "Create a new parameter" 3. Edit in the "Create Parameter" popup 4. After saving, a Parameter will show up in the Data Pane 5. Show the Parameter. The parameter will change the Bin you created.

# T/F Bins can be used in calculations

False

# T/F You can only bin data from relational datasets

True

You can create a Bin from a [**measure/dimension/both**]

both | (dimension must be numeric)

A Bin can be a [**measure/dimension/both**]

dimension only | (The bin dimension can be continous or discrete, though)

# T/F When you create bins from a measure you create a new measure.

False When you create bins from a measure you create a new **dimension**

A Bin can be [**continuous/discrete/both**]

both | (Image 7)

How can you make a dimension Bin?

Bins can only be dimensions. Trick question!

Bins are _ by default, so to turn them into _, you need to _

Bins are **discrete** by default, so to turn them into **continuous**, you need to **right click the field (in the data pane or the visual/one of the cards) and choose the continuous option**.

# T/F Measures are always green and Dimensions are always blue.

False. The color is based on Distrete or Continuous Measures and Dimensions can be both discrete and continous.

# T/F If a dimension is numerical (including dates), it can be green.

True | (Green = continuous)

# T/F In the Data Pane, measures are always green and Dimensions are always blue

False Remember, the color is based on Discrete or Continuous. You can convert a field from Discrete to Contiuous or visa versa in the Data Pane

# T/F A measures can be blue.

True, a measure can be blue if it's discrete.

# T/F A dimensions cannot be green.

False, a dimension can be green if it's continous. If a dimension is numerical (including dates), it can be continuous.

# T/F Dimensions with a data type of string/Boolean can be continous.

False | Image 7

# T/F Dates are the most common continous dimension.

True Dates are **the** most common continous dimension. Note, that this doesn't mean that dates are commonly CD, but CDs are commonly dates.

Dimensions with a string/Boolean data type cannot be turned into _. * a measure * discrete dimension * continous dimension

Answer: Continous * **Why Not Measure**: String/Boolean can turned into a measure (e.g. turing into a Count Distinct **Image 10**) * **Why Not Discrete**: both can only be discrete | (**Image 7**)

_ is the most common continuous dimension. * Decimal # * Whole # * Date & Time * Date * String

Date | (**Image 7**)

Based on Image 9, can you tell if the field is a measure/dimension and continuous/discrete?

measure/dimension = No continuous/discrete = Yes

What is discrete/continous? Image 9

Image 9

# T/F You cannot bin data from relational datasets

False You can only bin data from relational datasets

# T/F You can only bin data from a measure

False You can also bin data from a numerical dimension

You can only bin data from a ____ database * NoSQL * Relational * Non-relational

relational Note, NoSQL = Non-relational NoSQL has no tables, no rows ## Footnote https://www.pluralsight.com/resources/blog/software-development/relational-vs-non-relational-databases#:~:text=The%20non%2Drelational%20database%2C%20or,type%20of%20data%20being%20stored.

What's relational VS non-relational databases?

relational = rows & columns non-relational = NOT rows & columns

[Relational/Non-Relational] database is a database that stores data in tables, rows, and columns, with the ability to establish links or relationships between different tables.

Relational

____ is a database that does not use the tabular schema of rows and columns found in most traditional database systems.

non-relational

Partitions are ___

1 or more dimensions defining how to group the calculations ## Footnote https://campus.datacamp.com/courses/calculations-in-tableau/table-calculations-and-parameters?ex=1

1 or more dimensions defining how to group the calculations are called ____

Partitions

**Image 11** is partitioned by ____

product & year | (month is an addressing field)

The addressing field/s in **Image 11** is/are ____

Month | (Year and Product are called partitions)

What will happen to **Image 12** chart if you change the Running_SUM() to Window_SUM()?

**Image 13**

What's the list of Quick Table Calculations? | (pantrymaid)

Image 14 Hint: PanTRYMAiD . . . . . . . . Hint: 3T 3Y 3P 1MA

Is this likely made from a group or set. **Image 7**

set notice the In/Out **Image 8**

Is this likely made from a group or set. **Image 7 in Combination**

set notice the In/Out **Image 8 in Combination**

____ calculates the mean annual growth rate of a measure over a specified period of time longer than one year. 1. Compound Growth Rate 2. Moving Average 3. YTD Total 4. YOY Growth 5. YTD Growth

Compound Growth Rate (Note that CAGR is named this in the calculations tab) ## Footnote https://campus.datacamp.com/courses/calculations-in-tableau/time-series-analysis?ex=6

____ compares change in a measure to the same period last year (e.g. Q1]19 to Q1'18) 1. Compound Growth Rate 2. Moving Average 3. YTD Total 4. YOY Growth 5. YTD Growth

YOY Growth

____ calculates from the start of the year to the last known data point in that year. 1. Compound Growth Rate 2. Moving Average 3. YTD Total 4. YOY Growth 5. YTD Growth

YTD Total YTD Growth

YOY Growth requires continous date dimensions.

False YOY Growth requires **discrete** date dimensions.

____ calculated across a specified # of values bf and/or after the determined point in time 1. Compound Growth Rate 2. Moving Average 3. YTD Total 4. YOY Growth 5. YTD Growth

Moving Average

____ is used for smoothing fluctuating data 1. Compound Growth Rate 2. Moving Average 3. YTD Total 4. YOY Growth 5. YTD Growth

Moving Average