Boolean Functions

These functions are available for Calculated Fields and Crosstabs apart from IsRow and IsColumn, which are only appropriate for Crosstabs.

Simply used as an inserted value. It is not a function.

Simply used as an inserted value. It is not a function.

Function

Within( <Period> , <Date Expression> , <Date Expression> , <Expression> )

or

Within( <Period> , <Date Expression> , <Date Expression>, <Date Expression> , <Date Expression> , <Expression> )

Description Returns any record (row of data) that satisfies the supplied parameters. Primarily to be used where true date values are not in place e.g. a week number and a year number as separate fields.
Arguments

The period refers to the period of time to be used by the range expression.

This argument can contain DAYS, WEEKS, MONTHS, YEARS.

The first date expression is the date field to be used. This can be an actual date or a partial date.

The second date expression is the end point of the required date range e.g. if you want to return all data for the previous month, the end point of the range would be FIRST_DAY_OF_MONTH-1DAY.

The final expression is the range value of the within function. This is an array-based number and so will start at 0 (zero).

Additional Date Expressions can be used if only elements of a date are available as separate fields. In this case, the structure of the arguments is as follows:

  • Period (WEEKS, MONTHS, YEARS)
  • Date Expression (1st date element)
  • Date Expression (2nd date element)
  • Date Expression (1st date element end point)
  • Date Expression (2nd date element end point)
  • Expression (range value of the Within function)
Examples

  • Within( WEEKS , Sales_Date , LAST_DAY_OF_WEEK-1WEEK , 0) would return all rows that have the Sales Date that exists in the previous week.

    Usage would be CASE WHEN Within( WEEKS , Sales_Date , LAST-DAY_OF_WEEK-1WEEK , 0 ) THEN 1 END

  • Within( WEEKS , Sales_Date , LAST_DAY_OF_WEEK-1WEEK , 3) would return all rows that have the Sales Date that exists in the previous 4 weeks.

    Usage would be CASE WHEN Within( WEEKS , Sales_Date , LAST_DAY_OF_WEEK-1WEEK , 3) THEN 1 END

  • Within( WEEKS , Sales_Date_Week , Sales_Date_Year, LAST_DAY_OF_WEEK-1WEEK, LAST_DAY_OF_WEEK-1WEEK, 0) would return all rows that have the Sales Date that exists in the previous week
Function IsRow( <String Expression> )
Description References a row or nested row.
Arguments The string expression should be a case-sensitive reference to the name of a field value. The reference must be contained in single quotes. To reference a nested row value use comma separated string values.
Examples

  • IsRow(‘Total Sales’)

    Usage would be CASE WHEN IsRow(‘Total Sales’) THEN Total_Value END

  • IsRow(‘London’, ‘Total Sales’)

    Usage would be CASE WHEN Is Row(‘London’, ‘Total Sales’) && Area = ‘London’ THEN Total_Value END
Function IsColumn( <String Expression> )
Description References a column or nested column.
Arguments The string expression should be a case-sensitive reference to the name of a field value. The reference must be contained in single quotes. To reference a nested column value use comma separated string values.
Examples

  • IsColumn(‘Total Sales’)

    Usage would be CASE WHEN IsColumn(‘Total Sales’) THEN Total_Value END

  • IsColumn(‘London’, ‘Total Sales’)

    Usage would be CASE WHEN IsColumn(‘London’, ‘Total Sales’) && Area = ‘London’ THEN Total_Value END
Function Matches( <Expression> , <Expression> )
Description Used within a CASE or IF statement, returns whether a field matches a specified value
Arguments The string expression can be a field or a static string value. This can be a date, a string or a numeric value.
Examples
  • Expression = Male CASE WHEN Matches( Gender , 'Male' ) THEN 1 ELSE 0 END Would return 1