Measures with calculated filters
Measures can have a filter defined by a script. A filter is used if a measure is to be calculated only for those rows of source data that meet some criteria. The script is run for each row of the source data before the calculation of the measure occurs. The script checks whether source rows meet one or more criteria. The script return value (Boolean type) controls whether or not the rows are used for the measure calculation. A 'False' return value means skip that particular row, while 'True' means accept it.
A filter script has access to the following variable:
- field list - SliceFields: TfcSliceFields
Let's look at an example:
We need to calculate a customer's prepayments. Prepayment is modelled as a negative balance, so we need to sum only negative balances, that is, to create a calculated measure on the "Balance" field with aggregate function "Sum". We also need to create a filter for this measure to accept only rows with Balance < 0:
begin
Result := SliceFields['Balance'].CurrentValue < 0; // test field with "Balance" name
end
The calculated filter can be applied both to regular and to calculated measures.
The filter is created on the "Filtering" page of the measure editor: