Domain 2: Explore and Analyze Data (41%) Flashcards
When would you use the date formats “d” vs. “dd” and so on in calculations?
d: Display the day as a number without a leading zero (1-31).
dd: Display the day as a number with a leading zero (01-31).
ddd: Display the day as an abbreviation (Sun, Sat).
dddd: Display the day as a full name (Sunday, Saturday).
ddddd: Display the date as a complete date (including day, month, and year), formatted according to your system’s short date format setting. The default short date format is m/d/yy.
dddddd: Display a date serial number as a complete date (including day, month, and year) formatted according to the long date setting recognized by your system. The default long date format is mmmm dd, yyyy.
aaaa: The same as dddd, only it’s the localized version of the string.
When would you use the week formats “w” vs. “ww”?
w: Display the day of the week as a number (1 for Sunday through 7 for Saturday).
ww: Display the week of the year as a number (1-54).
How would you use the month formats “m” vs. “mm” and so on in calculations?
M: Display the month as a number without a leading zero (1 12). If m immediately follows h or hh, the minute rather than the month is displayed.
MM: Display the month as a number with a leading zero (01-12). If m immediately follows h or hh, the minute rather than the month is displayed.
MMM: Display the month as an abbreviation (Jan-Dec).
MMMM: Display the month as a full month name (January-December).
MMMMM: Display the month as a single letter abbreviation (J-D)
oooo: The same as MMMM, but localized.
Why would you use “AM/PM” vs. “am/pm” in a calculation?
You would use “AM/PM” if you want those letters capitalized.
Describe what happens when you use the following in a calculation:
* S
* Ss
* OOO
* tttt
S: Display the second as a number without leading zeros (0 59).
Ss: Display the second as a number with leading zeros (00 59).
000: Display milliseconds. Use a period character as a separator before specifying milliseconds.
t t t t t: Display a time as a complete time (including hour, minute, and second), formatted using the time separator defined by the time format recognized by your system. A leading zero is displayed if the leading zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:mm:ss.
How would this calculation render in a viz: yy-mm-dd (dddd)?
18-01-04 (Thursday)
How would this calculation render in a viz: “Fiscal Quarter” q “of” yyyy?
Fiscal Quarter 1 of 1976
What is the default start-of-week in the US and the EU?
Sunday is the first day of the week in the US
Monday is the first day in the EU
How can you specify fiscal year start for a datasource?
How do you do this for a specific date Dimension field?
Datasource:
1) Right-click (Control-click on Mac) the data source in the Data pane to open the Date Properties dialog box.
2) Set the Fiscal year start field to the appropriate month
Date Dimension field:
1) In the Data pane, right-click a date dimension (Ctrl-click on Mac)
2) Select Default Properties > Fiscal Year Start.
Cite the format for a calculated field designating a field as a date field.
DATE([field])
This expression returns a date given a number, string, or date.
DATE automatically recognizes many standard date formats.
MAKEDATE is another similar function, but MAKEDATE requires the input of numeric values for year, month, and day.
Cite the format for a calculated field that adds a specified number of date parts (months, days, etc) to the starting date.
DATEADD(date_part, interval, [date])
Example: Add 280 days to the date February 20, 2021:
* DATEADD(‘day’, 280, #2/20/21#) = #November 27, 2021#
Cite the format for a calculated field that returns the number of date parts (weeks, years, etc) between two dates.
DATEDIFF(date_part, date1, date2, [start_of_week])
Example: Number of days between March 25, 1986 and February 20, 2021
* DATEDIFF(‘day’, #3/25/1986#, #2/20/2021#) = 12,751
Cite the format for a calculated field that returns the name of the specified date part as a discrete string.
DATENAME(date_part, date, [start_of_week])
Example: DATENAME(‘month’, #1986-03-25#) = “March”
A very similar calculation is DATEPART, which returns the value of the specified date part as a continuous integer. DATEPART can be faster because it is a numerical operation.
By changing the attributes of the calculation’s result (dimension or measure, continuous or discrete) and the date formatting, the results of DATEPART and DATENAME can be formatted to be identical.
An inverse function is DATEPARSE, which takes a string value and formats it as a date.
Cite the format for a calculated field that returns specifically formatted strings as dates.
DATEPARSE(date_format, date_string)
Example: DATEPARSE(‘yyyy-MM-dd’, “1986-03-25”) = #March 25, 1986#
The <date_format> argument describes how the <date_string> field is arranged. Because of the variety of ways the string field can be ordered, the <date_format> must match exactly.</date_format></date_string></date_format>
DATE is a similar function that automatically recognizes many standard date formats. DATEPARSE may be a better option if DATE does not recognize the input pattern.
MAKEDATE is another similar function, but MAKEDATE requires the input of numeric values for year, month, and day.
Inverse functions, which take dates apart and return the value of their parts, are DATEPART (integer output) and DATENAME (string output).
Which calculated field function will return the name of the specified date part as an integer?
DATEPART(date_part, date, [start_of_week])
Which calculated field function can be thought of as date rounding?
DATETRUNC(date_part, date, [start_of_week])
It takes a specific date and returns a version of that date at the desired specificity. Because every date must have a value for day, month, quarter, and year, DATETRUNC sets the values as the lowest value for each date part up to the date part specified.
Example: the first day of the quarter containing 9/22/2018
* DATETRUNC(quarter, #9/22/2018#) = #7/1/2018#
Which functions do you use to display the number of a date part? Example: Sunday = 1, March = 3, etc.?
DAY(date)
WEEK(date)
MONTH(date)
QUARTER(date)
YEAR(date)
What does the ISDATE function do?
It returns a Boolean response: aka True/False
The required argument must be a string. ISDATE cannot be used for a field with a date data type—the calculation will return an error.
Example:
* ISDATE(09/22/2018) = true
* ISDATE(22SEP18) = false
When might you use the MAKEDATE function?
When you have separate fields that are numerical and refer to a date part. MAKEDATE Returns a date value constructed from the specified <year>, <month>, and <day>.</day></month></year>
Example:
* MAKEDATE ([year field],[month field],[day field])
Related:
MAKEDATETIME(date, time)
MAKETIME(hour, minute, second)
What calculated field functions return the date or time at the time of rendering?
NOW(): Returns date/time (Ex: NOW() = 1986-03-25 1:08:21 PM)
TODAY(): Returns current date. (Ex: TODAY() = 1986-03-25)
List the valid “date_part” arguments available in Tableau in calculated date fields.
‘year’
‘quarter’
‘month’
‘dayofyear’
‘day’
‘weekday’
‘week’
‘hour’
‘minute’
‘second’
What does the “start_of_week” argument in date calculated fields denote?
The start_of_week parameter can be used to specify what day is considered the first day of the week, such as “Sunday” or “Monday”. If it is omitted, the start of week is determined by the data source.
In calculated date fields, you use “#” instead of double quotes when you want exact characters to render - Why?
It tells Tableau that the value inside the symbols is a date. Otherwise, it would consider the characters to be a string.
What does the CONTAINS function do in calculated string fields?
Returns true if the given string contains the specified substring
CONTAINS(string, substring)
Example: CONTAINS(“Calculation”, “alcu”) = true
ENDSWITH(string, substring) and STARTSWITH(string, substring), though they look just at the end or startof strings.