Test 2 - Calculations

Question 1

What is the DATEDIFF syntax?

Answer 1

DATEDIFF(‘date_part’, date1, date2)

Question 2

What does the DATE function do?

Answer 2

Changes string and number expressions into dates, as long as they are in a recognizable format

Question 3

What function should you use if DATE does not recognize the input?

Answer 3

DATEPARSE

Question 4

What does DATENAME do?

Answer 4

Returns the requested date part of a given date as a string

Question 5

What is the DATENAME syntax?

Answer 5

DATENAME(‘date_part’, date)

Question 6

What does DATEPARSE do?

Answer 6

returns specifically formatted strings as dates

Question 7

What is the DATEPARSE syntax?

Answer 7

DATEPARSE(‘date_format’, “date_string”)

Question 8

What does DATEPART do?

Answer 8

returns the name of the specified date part as an integer

Question 9

What is the syntax for DATEPART?

Answer 9

DATEPART(‘date_part’, date)

Question 10

What does MAKEDATE do?

Answer 10

returns a date value constructed from the specified year, month, and day

Question 11

What is the syntax for MAKEDATE?

Answer 11

MAKEDATE(year, month, day)

Question 12

What is the syntax for CONTAINS

Answer 12

CONTAINS(“string”, “substring”)

Question 13

What is the syntax for LEFT?

Answer 13

LEFT(“string”, “number of characters”)

Question 14

What are three functions used to change casing?

Answer 14

LOWER, UPPER, PROPER

Question 15

What do LTRIM, RTRIM, and TRIM do?

Answer 15

Remove spacing from the respective sides

Question 16

What does the ZN function do?

Answer 16

Returns value if given expression isn’t null, or 0 if it is null

Question 17

What is the syntax for a type conversion function?

Answer 17

TYPE(expression)

Question 18

What is the syntax for REPLACE?

Answer 18

REPLACE(string, substring, replacement)

Question 19

What is the syntax for SPLIT?

Answer 19

SPLIT(string, delimiter, token number)

Question 20

What is the result of SPLIT (“a-b-c-d”, “-“, 2)?

Answer 20

b

Question 21

What is the result of SPLIT (“a|b|c|d”, “|”, -2)?

Answer 21

c

Question 22

What is CASE statement syntax?

Answer 22

CASE <expression>
WHEN <'value1'> THEN <'then1'>
WHEN <'value2'> THEN <'then2'>
...
[ELSE <'default'>]
END</expression>

Question 23

What does IF NULL do?

Answer 23

Given syntax IFNULL(expr1, expr2), returns expr1 if non-null, otherwise returns expr2

Question 24

What do FIXED LOD calculations do?

Answer 24

compute a value using the specified dimensions, without reference to the dimensions in the view

Question 25

What are the Quick Table calculations available?

Answer 25

1. Running total 2. Difference 3. Percent difference 4. Percent of total 5. Rank 6. Percentile 7. Moving average 8. YTD total 9. Compound growth rate 10. Year of year growth 11. YTD growth

Question 26

Which table calculation returns the number of rows from the current row to the first row in the partition?

Answer 26

FIRST()

Question 27

Which table calculation returns the index of the current row in the partition, without any sorting with regard to value?

Answer 27

INDEX()

Question 28

Which table calculation returns the value of the expression in a target row, specified as a relative offset from the current row?

Answer 28

LOOKUP()

Question 29

Which table calculation might return the following rankings : 1,1,1,4,4,6,7,7,7,10?

Answer 29

RANK()

Question 30

Which table calculation might return the following rankings : 1,1,1,2,2,3,4,4,4,5?

Answer 30

RANK_DENSE()

Question 31

Which table calculation might return the following rankings : 3,3,3,5,5,6,9,9,9,10?

Answer 31

RANK_MODIFIED()

Question 32

Which table calculation might return the following rankings : 1,2,3,4,5,6,7,8,9,10?

Answer 32

RANK_UNIQUE()

Question 33

Which table calculation returns rankings as percentiles?

Answer 33

RANK_PERCENTILE()