Section 1
Preview this deck
DatePart
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
6
All-time users
6
Favorites
0
Last updated
4 years ago
Date created
Mar 1, 2020
Cards (79)
Section 1
(50 cards)
DatePart
Returns integers from dates. DatePart(Year/Month/Day/Hour/Minute/Second, [date attribute]) DatePart(Day,[Time.Date])
NumRows
Returns the total number of rows in the current result set. This function is not available in ETL Services. NumRows() NumRows()
Median
Returns the value in the middle of all measure values at the report grain. An optional break by attribute denoted with "By" can be used. Median([logical column] By [logical attribute column]) Median([OrderDate: Sum: Quantity] By [Product.Product Line])
ATan2
Returns the arc tangent of x and y. ATan2(x,y)
DateDiff
Returns the difference in dates as an integer value. (NULLS are interpreted as 0. ) DateDiff(Year/Month/Day/Hour/Minute/Second, [start date],[end date]) DateDiff(Month,[Order_Details.OrderDate], [Order_Details.RequiredDate])
LookupValue
Lookup based on value in the current result set to get a dimension or measure value from another resultset. Looks for EXACT MATCH. LookupValue (column number from the query, [value in the current query to look at], column number of the new query, Select [new query] from [All]) lookupvalue(0,[Products.CategoryName],1, Select [Products.CategoryName],[OrderDate: Sum: Quantity] from [All])
Add
Allows you to add a value to end of an existing list. Add([ListVariable],10)
Log(x)
Log base e of X. Returns the logarithm of a given number in a specified base. Log([logical column]) Log([OrderDate: Sum: Quantity])
IIF
Immediate IF. Allows an expression to return one result if the specified condition is true and a different one if it is false. Can be nested. IIF(evaluation,result if TRUE, result if FALSE) IIF([OrderDate: Sum: Quantity] >6000, 0, [OrderDate: Sum: Quantity])
GetDayID
This function allows you to return a unique integer signifying the day based on a date or datetime value. [ID Value] = GetDayID([DateValue])
NowDate
Returns current date. You can specify the format to use in the column properties. NOWDATE 2014-03-26
Ln(x)
Returns the natural logarithm of a given number in a specified base. Ln([logical column]) Ln([OrderDate: Sum: Quantity])
IFNULL
Allows an expression to return a different result if it is null. IFNULL(evaluation,result if NULL) IFNULL([OrderDate: Sum: Quantity],0)
Floor(x)
Returns the closest integer less than or equal to a given number. Floor([logical column]) Floor([OrderDate: Sum: Quantity])
AddParentChild
Adds a single parent-child pair to the current list of pairs. AddParentChild([ParentID],[ChildID])
Length
Returns the number of characters in a string. This can also be used in a Birst ETL script to return the number of elements in a List object. Length([dimension]) Length([Products.CategoryName]) ETL Script Example: Length([myList]) - 1
GetMonthID
This function allows you to return a unique integer signifying the month based on a date or datetime value. [ID Value] = GetMonthID([DateValue])
DateAdd
Add or subtract an integer from a date. DateAdd(Year/Month/Day, integer, [Date Attribute]) DateAdd(Day, 5,[Time.Date])
ArcCos
Returns the arc cosine of a number between ο and π. ArcCos([logical column]) ArcCos([OrderDate: Sum: Quantity])
GetLevelValue
For the current pair, GetLevelValue(n) returns the ID n levels deep into the hierarchy. If there are fewer than n levels, it returns the ID of the lowest level available. Hence, the IDs will repeat for levels lower than their depth.
Let
Allows re-use of logic in complex expressions by declaring expression-level variables, initializing them and re-using them in the expression. Let(variable declarations,expression body) Let(Dim [AvgPrice] As Float = LookupValue(0, [Categories.CategoryName],1,Select [Categories.CategoryName],[OrderDate: Avg: UnitPrice] from [All]) Dim [TotalQuantity] As Float = LookupValue(0,[Categories.CategoryName],1,Select [Categories.CategoryName],[OrderDate: Sum: Quantity] from [All]), IIF([AvgPrice]<25,25,[AvgPrice])*[TotalQuantity])
Log10(x)
Log base 10 of x. Log10([logical column]) Log10([OrderDate: Sum: Quantity])
GetWeekID
This function allows you to return a unique integer signifying the week based on a date or datetime value. [ID Value] = GetWeekID([DateValue])
Degrees
Converts x radians to degrees. Degrees(x)
DRank
Dense Rank returns the rank of each row within the result set, without any gaps in the ranking. DRank([measure]) DRank([OrderDate: Sum: Quantity])
Alt:
To use new query language syntax in spaces using classic query language. alt:{expression} alt:Median([OrderDate: Sum: Quantity] by [Time.Year])
Ceiling(x)
Round up. Returns the closest integer greater than or equal to the given number. ceiling([measure]) ceiling([OrderDate: Sum: Quantity])
Now
Returns current date and time. You can specify the format to use in the column properties. NOW 2014-03-26 14:29:19
GetPromptFilter()
A place holder for a dashboard prompt. See Passing Prompt Filters and Values to Expressions and Custom Formulas. GetPromptFilter({prompt name}) [OrderDate: Sum: Quantity] WHERE GetPromptFilter('MyPrompt')
GetVariable()
Retrieves the value of a variable. GetVariable({variable name}) GetVariable('LoadDate')
NextChild
Once a set of parent-child pairs have been added, the list can then be iterated over one by one to flatten. NextChild positions the current record to the next available pair. If there are no more pairs left, it returns false.
Find
Similar to LookupValue, except the lookup value does not need an exact match. If the match is exact that row is returned, otherwise, the first row that is greater than the lookup value is used. Parameters can be added before the logical query and referenced in the logical query using the %index syntax, starting with %0. Find({index of column to lookup},{value to lookup},{index of column to return}, [query]) Find(0,[Products.ProductName],1,Select [Products.ProductName], [OrderDate: Sum: Quantity] From [All])
LookupRow
Use over LoopupValue if the result set will be in the same order as the existing set. LookupRow (index of the row to return, index of the column to return, SELECT [Query] from [ALL]) LookupRow (0,1, Select [OrderDetail.OrderID], [OrderDate: Sum: Discount] from [ALL])
FunctionLookup
Custom functions execute a script over an entire result set to return a result. It works by declaring a return data type (the type of the function) and providing both a script and a logical query. The script is executed for each row of the result set. Additionally, a lookup parameter can be added and will run the function only on rows that satisfy the filter for the targeted column being equal to the value set. The variable [Result] is populated with the return value. FunctionLookup(return data type,lookup parameter,script statement block,logical query)
ArcTan
Returns the arc tangent of a number between -π/2 and π/2. ArcTan([logical column]) ArcTan([OrderDate: Sum: Quantity])
Format
Formats a number or date to the particular format provided. Format([measure]/[date]/[string]) Format([OrderDate: Sum: Quantity],'##,###,##0.00')
Function()
Allows for creation of a custom function that can be used in any logical query including an expression. Function({datatype of return value}, {calculation/script} COMPLETE [Result] = {calculation from script} END COMPLETE) Function (Float, Dim [Var1] as Float = 0.0 Dim [Var2] as Float = 0.0 Complete [Result] = [Var1] - [Var2] End Complete)
Order By
Sort function. Select [query] from [All] Order By [dimension]/[measure] Ascending/Asc/Descending/Desc Select [Product.Category Name], [Time.Year],[Unit Sales] from [All] Order By [Product.Category Name] Ascending, [Time.Year] Descending
Display Where
A display filter. Does the filtering on the returned dataset (the data in cache). Can be used on both dimension columns and measures. Returns entire query but only displays data matching criteria. Select [query] from [All] Display Where [measure]/[dimension]=value Select[Sales] from [All] Display Where [Sales]>10000
Cos
Returns the cosine of a given angle. Cos([logical column]) Cos([OrderDate: Sum: Quantity])
GetPromptValue()
A place holder for a user defined value taken from a dashboard prompt. An optional second argument (such as null or a string such as '2012') can be used in the event that the first argument is not a currently defined prompt. The second argument can also be supplied by a variable using GetVariable(). Note that all values are returned as strings (except null) and must be cast to numeric data types if they are to be used in arithmetic functions. See Passing Prompt Filters and Values to Expressions and Custom Formulas. GetPromptValue({prompt name},{default}) [OrderDate: Sum: Quantity]* Float(GetPromptValue('MyPrompt','2012'))
CAST
Convert data types. Expressions and variables can be cast to different data types. All data types implicitly cast to a varchar so no CAST statement is needed. In the case where the type is not cast-able (e.g., casting 'A' to a float) a NULL is returned. Float() Integer() Date() DateTime() Integer(GetPromptValue('Stage 4 Probability'))
Dtop, Display Top
Displays top N results of the returned dataset. To get the top N from the database, see Top N. Select Dtop x [query] from [all] Order By [dimension]/[measure] Ascending/Asc/Descending/Desc To display the top 5 results of a newly-created expression: SELECT DTOP 5 USING OUTER JOIN [Time.Year] 'F9', [Order_Details.ContactName] 'F11',[OrderDate: Sum: Quantity] 'OrderDate: Sum: Quantity',[OrderDate: Sum: UnitPrice] 'OrderDate: Sum: UnitPrice', [OrderDate: Sum: Quantity]* [OrderDate: Sum: UnitPrice] 'F19' FROM [ALL] ORDER BY [EXPR.F19] ASCENDING NULLS LAST
GetLevelAttribute
Provides additional data either on the entire tree or on a given record that is being processed. Two options are supported. Option 1: GetLevelAttribute('NumLevels') // Returns the maximum depth of items in the tree. Option 2: GetLevelAttribute('CurrentDepth') // Returns the depth of the current item in the tree.
Exp
Returns e raised to the power of the number given. Exp([logical column]) Exp([OrderDate: Sum: Quantity])
Abs(x)
Absolute value of a measure value. abs([measure]) abs([OrderDate: Sum: Quantity])
LTrim
Trims white space from x on the left. LTrim(x)
ArcSin
Returns the arc sine of a number between -π/2 and π/2. ArcSin([logical column]) ArcSin([OrderDate: Sum: Quantity])
IsNaN
Evaluates an expression and returns true if it is not a number and false if it is. The result can be used as a conditional check. IsNaN(evaluation) IIF(IsNaN(evaluation), result if TRUE, result if FALSE) IIF(IsNaN([OrderDate: Sum: Quantity]),0, [OrderDate: Sum: Quantity])
IsInf
Evaluates an expression and returns true if the value is +/- infinity. The result can be used as a conditional check. IsInf(evaluation,result if TRUE, result if FALSE) IIF(IsInf([OrderDate: Sum: Quantity]),0, [OrderDate: Sum: Quantity])
Section 2
(29 cards)