Data Functions in Bex Query Designer
Hi All,
I have made an attempt to explain some of the data functions which can be used as operands when we define formula or Calculated Key Figure
1) COUNT(<Expression>)
This data function is used when we would like to display value of the count made and it returns value 1 if the <expression> is <> 0, otherwise it would return
value as 0
Eg: To display Count of Open Sales orders.
2) DATE
The value is seen as the number of days from 01.01.0000 onwards and is output as a date.
The value can be between 0 and one million, which results in a date between 01.01.0000 and 27.11.2738.
3) NDIV(<Expression>)
It is used when we do some calculations based on division taking into account numerator and denominator. This function returns value 0 if the expression causes a division by 0, otherwise the result is the value of the expression. This is used to avoid error messages or so that we can continue to calculate with a defined result.
E.g: NDIV{5/(2-2)} would result value as 0.
4) NOERR(<Expression>)
Returns 0 if the calculation of <expression> leads to an arithmetical error. Otherwise the result is the value of the expression. Used to avoid error messages or so that you can continue to calculate with a defined result.
E.g: NOERR(sqrt(-1)) = 0
5) SUMCT <Operand>
Returns the result of the operand to all rows or columns
Example table for operator SUMCT <Operand>
Year |
Region |
Sales |
SUMCT “Sales” |
1999 |
North |
30 |
60 |
|
South |
30 |
60 |
|
Result for 1999 |
60 |
180 |
2000 |
North |
60 |
120 |
|
South |
60 |
120 |
|
Result for 2000 |
120 |
180 |
Overall Result |
|
180 |
180 |
6) SUMGT <Operand>
Returns the overall result of the operand
Example table for operator SUMGT <Operand>
Year |
Region |
Sales |
SUMGT “Sales” |
1999 |
North |
30 |
180 |
|
South |
30 |
180 |
|
Result for 1999 |
60 |
180 |
2000 |
North |
60 |
180 |
|
South |
60 |
180 |
|
Result for 2000 |
120 |
180 |
Overall Result |
|
180 |
180 |
7) TIME
The TIME operator takes any key figure or (partial) formula and makes a key figure of type TIME
For example, the formula is then TIME(Sales).
The system cuts off the decimal places, interprets the value as seconds, and displays the value in format +-hh:mm:ss. For example, 4812 is displayed as 1:20:12