Logical Operator Definitions
Add
Adds the derivative member’s value by the value set in the Math Value field.
Subtract
Subtracts the value specified in the Math Value field from the derivative member’s value.
Multiply
Multiplies the derivative member’s value by the value specified in the Math Value field.
Divide
Divides the derivative member’s value by the value specified in Math Value. You can also use a buffer value as shown in the following image:
In this case, accounts named TicketCost in the load file are targeted. These accounts are renamed to Math_TCPerNPS.
This is the complex expression that performs the division. The MathBufferValue amount is captured for the NPS account. The values for the original TicketCost account records are then divided by the value of the NPS account:
Lag (Years)
Returns the past value (lag) per the number of years set in the Math Value field of the rule. Math Value = 1 by default. The lag operator looks at the first date in the set, then returns the lag value for the number of years specified. In the data set shown below, the value 12 would be returned if the Lag (Years) value is set to 1:
Lag (Months)
Returns the past value (lag) per the number of months set in the Math Value field of the rule. Math Value = 1 by default. The lag operator looks at the first date in the set, then returns the lag value for the number of months specified. In the data set shown below, the value 9.99 would be returned if the Lag (Months) value is set to 1:
Lag (Days)
The Lag (Days) logical operator returns the past value (lag) per the number of days set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date. The lag operator looks at the first date in the set, then returns the lag value for the number of days specified. In the data set shown below, the value 23.39486017 would be returned if the Lag (Days) value is set to 2:
Lag (Hours)
Returns the past value (lag) per the number of hours set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date/time. The lag operator looks at the first date/time in the set, then returns the lag value for the number of hours specified. In the data set shown below, the value 43.53177343 would be returned if the Lag (Hours) value is set to 2:
Lag (Minutes)
Returns the past value (lag) per the number of minutes set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date/time. The lag operator looks at the first date/time in the set, then returns the lag value for the number of minutes specified. In the data set shown below, the value 21.12 would be returned if the Lag (Minutes) value is set to 4:
Lag (Seconds)
Returns the past value (lag) per the number of seconds set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date/time. The lag operator looks at the first date/time in the set, then returns the lag value for the number of seconds specified. In the data set shown below, the value 17.77 would be returned if the Lag (Seconds) value is set to 3600 (60 minutes):
Lag Change (Seconds)
Returns the difference between the latest value and a past value (lagged value), per the number of seconds set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of seconds set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value -2.25 would be returned if the Lag Change (Seconds) value is set to 3600 (60 minutes):
Lag Change (Minutes)
Returns the difference between the latest value and a past value (lagged value), per the number of minutes set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of minutes set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value -5.60 would be returned if the Lag Change (Minutes) value is set to 4:
Lag Change (Days)
Returns the difference between the latest value and a past value (lagged value), per the number of days set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of days set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value 8.85 would be returned if the Lag Change (Days) value is set to 3:
Lag Change (Months)
Returns the difference between the latest value and a past value (lagged value), per the number of months set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of months set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value -3.37686333 would be returned if the Lag Change (Months) value is set to 1:
Lag Change (Years)
Returns the difference between the latest value and a past value (lagged value), per the number of years set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of years set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value 3.37686333 would be returned if the Lag Change (Years) value is set to 1:
Lag Change (Step Back)
First, sorts a data set in descending order by the Created date. Then, it “Steps Back” from the first record in the data set and selects the record based on the number of positions back specified in the Math Value. The first record in the data set is position zero. In the data set below, the difference between the 1st record and the 6th record is returned:
Lag (Step Back)
Sorts a data set in descending order by the Created date and “steps back” from the first record in the data set, selecting the record based on the number of positions back specified in the Math Value. In the example below, the value of the 6th record (sorted descending) in the data set is returned:
Lag Change (Hours)
Returns the difference between the latest value and a past value (lagged value), per the number of hours set in the Math Value field. The data set is first sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of hours. The difference between the latest value and the lag value is returned.
In the data set shown below, the value -28.02 would be returned if the Lag Change (Hours)Math Value is set to 2, where 15.5154439 - 43.53177343 = -28.02.
Absolute Value
Generates a new record that contains the absolute value of the values in the load file.
Round (Precision)
Generates a new record that contains the rounded value of the values in the load file. The Math.Round(decimal, int) method is used. The Math Value is the number of decimal places in the return value. It rounds a decimal value to the specified number of decimal places and rounds midpoint values to the nearest even number.
Modulo (Divisor)
Calculates the modulo (remainder after division) value. For example, 12/10 modulo = 2. The divisor is set in the Math Value field within the Derivative Expression Editor dialog box. The default divisor value is 10.
Power (Exponent)
Generates a new record that contains the exponential value of the values in the load file. For example, 5 to the power of 2 is 5 x 5 = 25. The power value is set in the Math Value field of the Derivative Expression Editor dialog box.
Proportion (Count)
Calculates the proportion of 1 out of the total number of rows that were created. For example, in the rule expression A#[TicketCost]=Stats_PropCount:A1#[]=None:A2#[]=None:xBDT#[2022-01-01~2022-12-31]!Y, a derivative record is created for each record where the account = TicketCost and that fall within the date range 2022-01-01 - 2022-12-31. If 54 total rows are created, the proportion count value will equal 1/54 = 0.018518519.
Proportion (Value)
Calculates the proportion of the value of a field out of the total for that column, where Value / Sum of column = Proportion (Value). For example, in the rule expression A#[TicketCost]=Stats_PropValue:A1#[]=None:A2#[]=None:xBDT#[2022-01-01~2022-12-31]!Y, a derivative record is created for each record where the account = TicketCost, and that falls within the date range 2022-01-01 to 2022-12-31.
Clip (Lower Bound)
Sets a minimum lower threshold for records that are created as a result of this rule. The default value is 10. Values less than the lower bound are set to the lower bound value. For example,if the original value is 9, the value is set to 10 in the derived record.
Clip (Upper Bound)
Sets an upper threshold for records that are created as a result of this rule. The default value is 35. Values greater than the upper bound are set to the upper bound value. For example, If the original value is 36, the value is set to 35 in the derived record.
Count
First DateTime
Creates one record in the BI Blend table that shows the value for the earliest created date/time of the records matching the rule expression.
Last DateTime
Creates one record in the BI Blend table that shows the value for the latest date/time of the records that match the rule expression.
Minimum Value
Creates one record in the BI Blend table that shows the lowest value for the records that match the rule expression.
Maximum Value
Creates one record in the BI Blend table that shows the highest value for the records that match the rule expression.
Average
Creates one record in the BI Blend table that shows the average value for the records that match the rule expression.
Median
Creates one record in the BI Blend table that shows the median value for the records that match the rule expression.
Mode
Creates one record in the BI Blend table that shows the mode value for the records that match the rule expression. The mode is the value that appears the most number of times in a data set. If there are multiple instances of a mode in a file, the first value is returned if the values are sorted ascending. This is also done for the mode calculation in Excel.
Standard Deviation
Creates one record in the BI Blend table that shows the standard deviation value for the records that match the rule expression. It uses the standard deviation based on the entire population. Numbers can be validated by using the STDEV.P function in Excel.
Col DateDiff (Days)
Returns the number of days within the BeginDate and EndDate specified in the load file. The value is rounded down to the nearest whole number. The Math Value syntax, //xDt1;xDt2, indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source:
BI Blend DateTime dimensions are set on the cube:
Here is an example of a rule expression for this operator: A#[TicketCost]=Stats_DateDiffDays1and2:A1#[]=None:A2#[]=None:xBDT#[2022-01-01 00;00;00~2022-12-31 23;59;59]!D. This filters the data series to return account data between the specified date range, and sets the Group Level by day.
For all accounts named TicketCost in the load file, create a new record and do the following:
-
DateDiffDays.E: the .E portion of this rule name sets the Account Type to Expense.
-
Change account name to Stats_DateDiffDays1and2.
-
Set all A1 instances (Product column in db, Instance column from load file) to None.
-
Set all A2 instances (Customer column in db, Instance col from load file) to None.
Col DateDiff (Months)
Returns the number of months within the BeginDate and EndDate specified in a load file. The value is rounded down to the nearest whole number. The Math Value syntax, //xDt1;xDt2, indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source (see Col DateDiff (Days).
Here is an example of a rule expression for this operator: A#[TicketCost]=Stats_DateDiffMonths:A1#[]=None:A2#[]=None:xBDT#[2022-01-01 00;00;00~2022-12-31 23;59;59]!M. :xBDT#[2022-01-01 00;00;00~2022-12-31 23;59;59] filters the data series to return account data in the date range specified. !M sets the Group Level by Month.
Col DateDiff (Years)
Returns the number of years within the BeginDate and EndDate specified in a load file. The value is rounded down to the nearest whole number. The Math Value syntax, //xDt1;xDt2, indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source.
Col DateDiff (Hours)
Returns the number of hours within the BeginDate and EndDate specified in a load file. The value is rounded down to the nearest whole number hour. The Math Value syntax, //xDt1;xDt2, indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source:
Col DateDiff (Minutes)
Returns the number of minutes within the BeginDate and EndDate specified in a load file. The value is rounded down to the nearest whole number minute. xDt1 and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source:
Col DateDiff (Seconds)
Returns the number of seconds within the BeginDate and EndDate specified in a load file. xDt1 and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source:
Log (P+1)
Generates a new record that contains the natural log (1+x) value of the values in the load file. The following function is used in the calculation: Math.Log() function. Natural Log (1+x).
NOTE: The formula reference in BI Blend for the Log P1 function is not calculated for values <=1 and returns null in that case.
Apply Suffix or Prefix
Renames accounts to either MyPrefix_TicketCost or TicketCost_MySuffix using the following expressions: A#[TicketCost]>>MyPrefix_ or A#[TicketCost]<<_MySuffix, respectively.
Create If (> x)
Creates a record in the BI Blend table for each record where the Actual Value is greater than the value set in Math Value.
Create If (< x)
Creates a record in the BI Blend table for each record where the Actual Value is less than the value set in Math Value.
Filtering Records with Rule Expressions
Rule expressions in transformation rules can be used to filter records by date and time ranges using xBDT syntax. When data is loaded, derivative records are created for records that fall within the specified date and time range. Here are some examples:
xBDT#[2022-01-01 00;00;00~2022-01-31 23;59;59]: Creates derivative records for imported records that are >= start date/time and <= finish date/time.
xBDT#[CurrentDateTimeLocal~D+6]: Creates derivative records for imported records that are greater than the current date/time plus 6 days.
xBDT#[D-90~2022-03-31 23;59;59]: Creates derivative records for imported records that fall within the range of the specified date/time minus 90 days. Records equal to the current date/time are imported as well.
Sub-String Grouping in Rule Expressions
Syntax used in a rule expression can capture a specified number of characters from a string, starting with the character in the first position. In the example below, the first 6 characters of an account name are captured and applied in derivative records. Loaded records with an account name TicketCost will show an account name of Ticket in the derived records.
A#[TicketCost]@6
Syntax used in a rule expression can capture a sub-string between the first and last characters of a string. In the example below, a 4 character sub-string is captured starting at the 7th character of the string. Loaded records with an account name TicketCost will show an account name of Cost in the derived records.
A#[TicketCost]@7,4
