XWiki - Technical Documentation.CXAIR.User Guide.02\. Reporting.2c\. Crosstabs.2ci\. Calculation Builder

Used in front-end reporting and Index configuration, the CXAIR Calculation Builder provides an interactive interface to create additional fields, configure re-usable variables and utilise preconfigured functions to control data structures and reporting output.

6

7

New calculations can be derived when creating a report or at Index build-time. This guide will cover both front-end reporting techniques and, for system administrators, examples that have been used to enhance customer Indexes for end-users.

= Interface =

In the front-end, the Calculation Builder is accessed when creating Crosstab totals. There are two distinct uses when reporting: Measure Calculations and Cell Calculations.

12

13

== Measure Calculations ==

14

15

Using the calculation as a **Measure** allows users to perform calculations at row-level based on the **Total Type** rather than a direct cell calculation using existing totals.

16

17

In the **Totals** tab, select a **Total Type** then select **Calculation** from the subsequently revealed **Measure** drop-down list:

18

19

[[image:CalcScreen1.png||height="293" width="600"]]

20

21

Clicking the **…** icon will open the Calculation Builder:

22

23

24

[[image:CalcScreen2a.png||height="328" width="600"]]

25

26

On the left of the pop-up window, a list of available **Fields** and **Filters** are displayed that can be directly referenced when building calculations, along with **Functions**, **Values** and **Syntax** drop-down lists.

27

28

Use the **Functions** list to select from the available calculation functions. Simply replace the template values contained within **<>** with actual values required for the function.

For example:

Floor(<Expression>) becomes Floor(Sales_Price)

33

34

Select an option from the **Values** list to add dynamic values based on the currently loaded Index.

35

36

The **Syntax** drop-down list contains a number of common structures to assist with the creation of calculations within the tool. It is recommended that users familiarise themselves with the structures found in this list when first using the Calculation Builder.

37

38

To test the validity of the currently entered syntax, click the **Validate** button.

39

\\A green tick icon will denote when the syntax is correct, allowing the **Apply** button to be clicked and the total added to the report.

40

41

Click the **Clear** button to remove all text, or to cancel any changes made to the calculation, click **Cancel**.

42

43

=== Saving & Loading Calculations ===

44

45

To save a calculation, click the **Save** button located at the bottom-right corner.

46

47

Saved calculations can be made available for other users who have access to the same Index by selecting the **Available as Aggregation** option. Type a **Name** in the textbox and click **Save**. To discard changes, click the **Cancel** button.

48

49

After saving a global calculation it will then be displayed under the Crosstab **Measure** drop-down list for use in any future reports using the same Index, or by other users with the relevant permissions.

50

51

To remove or edit a global calculation, open the Measure Calculation Builder and click the **Manage** button. This will display a list of any saved and global calculations. Click the **Load** button to load the calculation into the Calculation Builder for editing or click on the **Remove** button to delete the calculation.

52

53

=== Adding Comments ===

54

55

To add comments to a calculation, wrap the required comment in /*. For example:

56

57

/*This calculation brings back CASH only payments*/

58

59

== Cell Calculations ==

60

61

Using Cell Calculations, users can perform calculations based on existing totals that have already been specified in the report.

62

63

In the **Totals** tab, select Cell/Total Calculation from the **Total Type** drop-down list:

64

65

[[image:CalcScreen3.png||height="293" width="600"]]

66

67

Clicking the **…** icon will open the Calculation Builder:

68

69

[[image:CalcScreen4a.png||height="330" width="600"]]

70

71

Rather than a list of available fields, previously defined totals are now displayed. This allows calculations between totals to be defined that are run against the Index at report run-time.

72

73

To reference totals, their position or name can be used with the **Total** function. This uses the following syntax:

74

75

**Total( <Expression> )**

76

77

For example, **TOTAL(0)** can be used to reference the first total, with an incremental numeric value assigned to each total in turn. Alternatively, if the total has been named, the **Total** function is not required and the name in single quotes can be used, such as **'Sales'**.

78

79

Use the **Count** cell calculation functions to perform these aggregation functions while referencing the values from other totals. These functions will only appear in the cell calculation builder and use the following syntax, with the 'Expression' text representing the calculation logic that will be wrapped in the function:

80

81

**Count( <Expression> )**

82

83

To reference the actual runtime position of a total, use the following syntax:

**Total (n,True)**

Enter a value to reference the total position. This is especially useful when assigning saved queries to totals and calculating across them.

88

89

For a full list of functions available for cell calculations, see the Calculations Function Index.

== DrillThru ==

To translate the calculation logic into syntax that can be parsed in the Query screen, the total logic or field must be wrapped in the **DrillThru** function.

For example:

**IF**

IF (Area_Name='Birmingham and North Midlands') {DrillThru(Cost_Price)}

99

100

IF (Area_Name='Birmingham and North Midlands') {DrillThru(1)}

101

102

**CASE**

103

CASE WHEN Area_Name = 'Birmingham and North Midlands' THEN DrillThru(Cost_Price) END

104

105

CASE WHEN Area_Name = 'Birmingham and North Midlands' THEN DrillThru(1) END

106

107

When referencing more than one total in a calculation, the **DrillThru** function is used to select the value that informs the drill operation.

108

109

For example, when deriving percentage values, the **DrillThru** function can be used to specify the numerator or denominator value. The following Row Total calculation calculates the percentage of branded items:

110

111

(sum(CASE WHEN Product_Type = 'Branded' THEN 1 ELSE 0 END)/sum( 1 ))*100

112

113

The following calculation drills to the branded product transactions:

114

115

(sum(CASE WHEN Product_Type = 'Branded' THEN DrillThru( 1 ) ELSE 0 END)/sum( 1 ))*100

116

117

The following calculation drills to the unbranded product transactions:

118

119

(sum(CASE WHEN Product_Type = 'Branded' THEN 1 ELSE DrillThru( 0 ) END)/sum( 1 ))*100

120

121

The following calculation drills to both branded and unbranded product transactions:

122

123

(sum(CASE WHEN Product_Type = 'Branded' THEN 1 ELSE 0 END)/sum( DrillThru( 1 ) ))*100

124

125

= Conditional Statements =

126

127

When creating calculations, utilising conditional statements is key to shaping output in both reporting and Index management.

128

129

== CASE Statements ==

130

131

CASE statements use the following syntax:

132

133

CASE WHEN <Condition> THEN <Expression> ELSE <Expression> END

134

135

To populate the calculation builder with the required syntax structure, select the **CASE** option from the **Syntax** drop-down list.

136

137

First, a condition is specified. An expression is then defined that will be output when the condition is true, or not true. A CASE statement must always finish with END.

138

139

For example, the following syntax will filter the output to only include cash sales:

140

141

CASE WHEN MOP = 'Cash' THEN 1 ELSE 0 END

142

143

//‘When the MOP field contains the value ‘Cash’, then count the rowotherwise ignore it.’//

144

145

Here, the condition is that the ‘MOP’ (method of payment) field equals ‘Cash’, and the expression ‘1’. This Boolean flag means that the rows will be included. The ‘ELSE 0’ expression is another Boolean flag that will instead omit any rows that do not equal ‘Cash’.

146

147

Notice how the MOP value is specified in single quotes. This is due to the field containing string values. Numeric fields used in calculations do not require single quotes.

148

149

The ‘1’ and ‘0’ values in this statement represent the calculation logic that will be applied, in this case either including or omitting the rows based on the condition. These values can be changed to suit the required report.

For example:

CASE WHEN MOP = 'Cash' THEN Sales_Price ELSE 0 END

154

155

//'When the MOP field equals ‘Cash’, then apply the selected **Total Type** to the ‘Sales Price’ field for these rows in the output. Otherwise, exclude rows with other MOP values.’//

156

157

Here, the ‘1’ value in the previous example has been swapped for ‘Sales_Price’. This will result in the selected **Total Type** for the total being applied to this field for all cash transactions. Other rows will be omitted.

158

159

Notice how the specified field has an underscore rather than a space. To work correctly, a field must be entered exactly as it is stored in the Index. To ensure the field is entered correctly, single-click the field of interest in the **Fields** list of the Calculation Builder.

160

161

=== Further Examples ===

162

163

|CASE

164

WHEN In( Acc_ACC_PURPOSE_CODE,'FA', 'FP', 'PP', 'HPO', 'REL' ) THEN 1

165

WHEN In( Acc_ACC_PURPOSE_CODE,'REN', 'RED', 'REO', 'REB') THEN 2

ELSE 7

END|CASE

WHEN

(C1_CPD_CUSTP_NATIONALITY = 'BRIT' ~|~| C1_CPD_CUSTP_NATIONALITY IS NULL)

170

&&

171

(Addr_ADDR_COUNTRY_CODE = 'UK' ~|~| Addr_ADDR_COUNTRY_CODE IS NULL)

THEN 'N'

ELSE 'Y'

END

== IF Statements ==

IF statements use the following syntax structure:

179

180

IF <Condition> { <Expression> } ELSE { <Expression> }

181

182

To populate the calculation builder with the required syntax structure, select the **IF** option from the **Syntax** drop-down list.

183

184

The syntax differs to a CASE statement, with the condition specified before defining expressions wrapped in curly brackets.

For example:

IF MOP = 'Cash' { 1 } ELSE { 0 }

189

190

//‘When the MOP field contains the value ‘Cash’, then count the row, otherwise ignore it.’//

191

192

This filters the output in the same way as the previously reviewed CASE statement.

193

194

Again, the ‘1’ and ‘0’ values in this statement represent the calculation logic that will be applied, in this case either including or omitting the rows based on the condition. These values can be changed to suit the required report.

For example:

IF MOP = 'Cash' { Sales_Price } ELSE { 0 }

199

200

//'If the MOP field equals ‘Cash’, then apply the selected Total Type to the ‘Sales Price’ field for these rows in the output. Otherwise, exclude rows with other MOP values.’//

201

202

As with the previous example, the ‘1’ value in the previous example has been swapped for ‘Sales_Price’. This will result in the selected **Total Type** for the total being applied to this field for all cash transactions. Other rows will be omitted.

= Dynamic Views =

A dynamic view is a distinct count of a pre-configured field setup by an administrator.

207

\\For example, adding a Dynamic View for an ‘Employee Name or ID’ would allow users to create reports that only count unique employees within their report.

208

209

When drilling through on a Dynamic View or when there is both an aggregation and a Dynamic View present, the first row found will be shown on the drill through results.

210

211

Use the **Dynamic View** drop-down list when creating Crosstab totals to select from the list of Dynamic Views added by the system administrator:

212

213

[[image:CalcScreen5.png||height="259" width="600"]]

214

215

Selecting **[Records]** from this list will perform the selected **Total Type** on the underlying rows with no distinct count applied.

216

217

**If a field doesn’t appear as an option under the Dynamic View drop list, contact you CXAIR administrator.**

218

219

Dynamic Views can also be used in the Query screen using the following syntax:

220

221

+<Field_Name>:"<Query>" +DYNAMICVIEW: "<Dynamic View Name>"

For example:

+Region_Name:"London" +DYNAMICVIEW: "Employees"

226

227

== Ordering Calculation Logic ==

228

229

Understanding how Dynamic Views filter the data is vital to ensuring reporting figures are valid.

230

231

When a Dynamic View is invoked, the Index is scanned row-by-row until a match is found. The order in which rows are scanned is based on the underlying structure of the Index, not necessarily the order in which the data was ingested.

232

233

When the first unique record is identified, every other row containing this identifier is discarded and not included in the output.

234

235

When using the **Dynamic View** drop-down list functionality in a standard total, the distinct count is run against the records before any calculation logic is applied. In some circumstances, this may remove records of interest.

236

237

By using the **CountDistinct** and **SumDistinct** calculation functions, the distinct count can instead be run against the records after the calculation logic has been applied.

238

239

The calculations use the following syntax:

240

241

CountDistinct( <Expression> , <String Expression> )

242

//Returns the distinct count from a dynamic view following the specified calculation logic.//

243

244

SumDistinct( <Expression> , <String Expression> )

245

//Sums the distinct count from a dynamic view following the specified calculation logic.//

246

247

The following report contains two totals counting the number of unique employees that have processed cash transactions broken down by region:

248

249

[[image:CalcScreen6.png||height="215" width="400"]]

250

251

Both totals employ the same dynamic view, titled ‘Employees’, but at different stages of the calculation.

252

253

The first total, ‘Cash Transactions 1’, has the Total Type set to Sum, the Dynamic View drop-down list set to Employees and the following calculation:

254

255

CASE WHEN MOP = 'Cash' THEN 1 END

256

257

When this calculation is run, the dynamic view filters to only the first row for each employee before then filtering to cash transactions. This means that many cash transactions have been filtered from the reporting output - only if the first row found for an employee contains a cash transaction is it included.

258

259

The second total, **‘Cash Transactions 2’**, instead has the **Total Type** set to Sum, the **Dynamic View** drop-down list set to **[Records]** and the following calculation:

260

261

CountDistinct( CASE WHEN MOP = 'Cash' THEN 1 END , 'Employees' )

262

263

This calculation first filters the rows based on the specified MOP value, then runs the distinct count against those records. This ensures all ‘Cash’ records are included in the result set before the distinct count of employees is run.

264

265

= Date Calculations =

266

267

When working with date fields in an Index, there are a number of ways values can be referenced to form the basis for new fields and calculated output when reporting.

268

269

== Reference Variables ==

270

271

When using date differentials in calculations, the following dynamic syntax can be used to add and subtract from date values:

272

273

(% border="1" cellspacing="0" class="GridTable1Light-Accent12" style="border-collapse:collapse; border:solid #90c5f6 1.0pt; width:593px" %)

274

(% style="height:1.0pt" %)|(% nowrap="nowrap" style="background-color:#1eb1ed; background:#1eb1ed; border-bottom-width:90c5f6; border-bottom:solid #59a9f2 1.5pt; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; width:444.45pt" width="593" %)(((

275

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

276

(% style="color:white; height:1pt; line-height:115%" %)**Point in Time**

277

)))

278

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

279

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

280

(% style="height:11.5pt; line-height:115%" %)NOW

281

)))

282

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

283

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

284

(% style="height:11.5pt; line-height:115%" %)TODAY

285

)))

286

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

287

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

288

(% style="height:11.5pt; line-height:115%" %)MONDAY

289

)))

290

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

291

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

292

(% style="height:11.5pt; line-height:115%" %)TUESDAY

293

)))

294

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

295

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

296

(% style="height:11.5pt; line-height:115%" %)WEDNESDAY

297

)))

298

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

299

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

300

(% style="height:11.5pt; line-height:115%" %)THURSDAY

301

)))

302

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

303

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

304

(% style="height:11.5pt; line-height:115%" %)FRIDAY

305

)))

306

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

307

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

308

(% style="height:11.5pt; line-height:115%" %)SATURDAY

309

)))

310

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

311

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

312

(% style="height:11.5pt; line-height:115%" %)SUNDAY

313

)))

314

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

315

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

316

(% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_DAY

317

)))

318

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

319

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

320

(% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_DAY

321

)))

322

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

323

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

324

(% style="height:11.5pt; line-height:115%" %)FIRST_MINUTE_OF_DAY

325

)))

326

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

327

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

328

(% style="height:11.5pt; line-height:115%" %)LAST_MINUTE_OF_DAY

329

)))

330

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

331

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

332

(% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_WEEK

333

)))

334

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

335

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

336

(% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_WEEK

337

)))

338

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

339

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

340

(% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_WEEK

341

)))

342

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

343

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

344

(% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_WEEK

345

)))

346

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

347

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

348

(% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_MONTH

349

)))

350

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

351

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

352

(% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_MONTH

353

)))

354

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

355

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

356

(% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_MONTH

357

)))

358

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

359

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

360

(% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_MONTH

361

)))

362

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

363

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

364

(% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_QUARTER

365

)))

366

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

367

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

368

(% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_QUARTER

369

)))

370

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

371

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

372

(% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_YEAR

373

)))

374

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

375

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

376

(% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_YEAR

377

)))

378

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

379

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

380

(% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_YEAR

381

)))

382

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

383

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

384

(% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_YEAR

385

)))

386

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

387

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

388

(% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_FISCAL_YEAR

389

)))

390

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

391

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

392

(% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_FISCAL_YEAR

393

)))

394

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

395

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

396

(% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_FISCAL_YEAR

397

)))

398

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

399

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

400

(% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_FISCAL_YEAR

401

)))

402

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

403

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

404

(% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_FISCAL_HALF_YEAR

405

)))

406

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

407

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

408

(% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_FISCAL_HALF_YEAR

409

)))

410

(% style="height:1.0pt" %)|(% nowrap="nowrap" style="background-color:#1eb1ed; background:#1eb1ed; border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

411

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

412

(% style="color:white; height:1pt; line-height:115%" %)**Differentials**

413

)))

414

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

415

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

416

(% style="height:11.5pt; line-height:115%" %)SECONDS

417

)))

418

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

419

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

420

(% style="height:11.5pt; line-height:115%" %)MINUTES

421

)))

422

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

423

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

424

(% style="height:11.5pt; line-height:115%" %)HOURS

425

)))

426

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

427

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

428

(% style="height:11.5pt; line-height:115%" %)DAYS

429

)))

430

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

431

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

432

(% style="height:11.5pt; line-height:115%" %)WEEKS

433

)))

434

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

435

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

436

(% style="height:11.5pt; line-height:115%" %)MONTHS

437

)))

438

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

439

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

440

(% style="height:11.5pt; line-height:115%" %)YEARS

441

)))

442

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

443

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

444

(% style="height:11.5pt; line-height:normal" %)YEAR_SAME_WEEK

445

)))

446

(% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((

447

(% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)

448

(% style="height:11.5pt; line-height:115%" %)WORKINGDAYS (When enabled by the system administrator)

449

)))

450

451

Please note that using the **SECONDS** differential over thousands of records will increase the processing time.

452

453

== Adding and Subtracting from Dates ==

454

455

Adding and subtracting from dates can be achieved a number of ways, such as wrapping the date field in the relevant point in time syntax and specifying a numeric value in the following format:

456

457

POINT IN TIME (<Date Field>)+<Numeric>DIFFERENTIAL

For example:

TODAY(Transaction_Date)+7DAYS

462

//Returns the referenced date field plus seven days.//

463

464

To reference numeric fields, use the following format:

465

466

POINT IN TIME(<Date Field>)+DIFFERENTIAL(<Numeric Field>)

467

468

For example:

469

NOW(Transaction_Date)+DAYS(Dispatch_Days)

470

//Returns the referenced date field and outputs field plus the number of months based on the numeric output of the calculation.//

471

472

LAST_DAY_OF_MONTH(SOC_CURR_PERIOD)-1MONTH

473

474

TODAY(LOAN_DATE)+MONTHS(Remaining_Term_in_Months)-1MONTH

475

476

Please note that **TODAY** will return the current date, while **NOW** will return the current date and time.

477

478

To reference calculations, use the following format:

479

480

POINT IN TIME(<Date Field>)+DIFFERENTIAL(<’Calculation’>)

For example:

FIRST_DAY_OF_MONTH(Transaction_Date)+WORKINGDAYS(‘Dispatch Days’)

485

//Returns the referenced date field and outputs the first day of the corresponding month plus the number of working days based on the numeric output of the calculation.//

486

487

Please note that calculations need to be referenced in single quotes.

488

489

== Reporting Examples ==

490

491

A range of functions are available in the calculation builder that allow two or more dates to be referenced on a single row, with the output tailored to suit specific time differentials.

492

493

=== Time in Department ===

494

495

The following report shows the minutes between arrival and departure using sample A&E data:

496

497

[[image:CalcScreen7.png||height="220" width="400"]]

498

499

This report contains a single total using the **DateBetween** function:

500

501

DateBetween( <Date Expression> , <Date Expression> , <TIMEPERIOD> )

502

//Returns the number of inclusive period values between two date expressions, except when a time is specified.//

503

504

First, the two date fields of interest are added and comma separated. Finally, the required time period is then added, also comma separated.

505

506

In this reporting example, the following calculation has been used:

507

508

DateBetween(Arrival_Date_Time,Left_Dept_Date_Time, SECONDS)

509

510

This compares two date fields on a single row and outputs the difference in seconds. The **Show Value As** drop-down list has then been set to Time Measure to format the output to HH:MM:SS. Please refer to the Reference Variables table earlier in the chapter to view the other possible differentials.

511

512

Furthermore, the dynamic point in time options also found in this table can be utilised when using the **DateBetween** function. For example:

513

514

DateBetween( TODAY, Closed_Date, MONTHS)

515

//Compares today’s date and the value in the Closed_Date field and returns a dynamic numeric value based on the date the report was run.//

516

517

=== Average Dispatch Time ===

518

519

The following report uses sample supermarket data to display the total number of orders, returns and the average number of days a before a product category order is dispatched:

520

521

[[image:CalcScreen8.png||height="289" width="550"]]

522

523

This report contains a single total using the **DaysBetween** function:

524

525

DaysBetween( <Date Expression> , <Date Expression>, <Optional Day(s)> )

526

//Returns the number of inclusive period values between two date expressions.//

527

528

First, the two date fields of interest are added and comma separated. Optionally, days that are not required can be added, also comma separated. This is most commonly used to omit weekends by including SATURDAY and SUNDAY in the calculation logic.

529

530

In this reporting example, the following calculation has been used with the **Total Type** set to **Average**:

531

532

DaysBetween(Order_Date,Ship_Date, SATURDAY, SUNDAY)

533

534

This compares two date fields on a single row and outputs the difference in days, omitting any rows that have a day value of Saturday or Sunday.

535

536

As the **Total Type** has been set to average, the report then displays the average dispatch time, omitting weekends. The total then uses a descending **Row Sort** to order the output for each product category.

537

538

In this example, conditional formatting has also been applied. Should a product category take longer to dispatch than the previous month, the total will be highlighted red. For more information regarding variance reports, please refer to the [[Crosstab Reporting Guide>>doc:Technical Documentation.CXAIR.Reporting Guides.09a\. Crosstab Reporting Guide.WebHome]].

539

540

=== Average Turnaround Time in Days & Working Days ===

541

542

The following report shows a small sample of mortgage applications along with two totals that display the average number of days taken for an application to be reviewed and accepted:

543

544

[[image:CalcScreen9.png||height="160" width="500"]]

545

546

This report contains two totals, using the previously detailed **DaysBetween** function and also the **CountWorkingDays** function:

547

548

CountWorkingDays( <Expression> , <Expression> )

549

//Counts the number of working days between two date fields.//

550

551

Please note that working days and holidays need to be configured by an administrator.

552

553

This function uses the same logic as the **DaysBetween** function, but does not allow an optional expression to be added that omits certain days. Instead, two date fields of interest are added and comma separated. This returns a count of working days, as set by the administrator.

554

555

In this report, while both totals have the **Total Type** set to Average, the first total, ‘Avg Turnaround Time (Days)’, uses the following calculation logic:

556

557

DaysBetween(Date_received,Date_accepted)

558

559

This returns a count of days between the **Date_received** and **Date_accepted** fields, before averaging the output in the report.

560

561

The second total, ‘Avg Turnaround Time (Working Days)’ instead uses the CountWorkingDays function:

562

563

CountWorkingDays(Date_received,Date_accepted)

564

565

The second total references the working days and bank holiday specified by the system administrator to automatically omit non-working days, providing an accurate total without the need for further calculations.

566

567

By setting the working days for the system, all report creators can leverage this function for any Index containing two or more date fields.

568

569

=== Time To Departure ===

570

571

The following report displays three different ways in which date differentials can be expressed:

572

573

[[image:CalcScreen10.png||height="154" width="600"]]

574

575

This report contains three totals. The first, ‘Time to Departure’, uses the **ToJulianDayTime** function:

576

577

ToJulianDayTime( <Date Expression> )

578

//Returns the Julian Daytime value of a datetime expression. The Julian daytime is the continuous count of seconds since the beginning of the Julian Period and is used when comparing two datetimes.//

579

580

In this reporting example, the following calculation has been used:

581

582

ToJulianDayTime( Departure_Datetime )-ToJulianDayTime( Attendance_Datetime )

583

584

By wrapping the datetime fields in the **ToJulianTime** function, the values are converted to numeric values. This has a key performance advantage over the previous example where the **DateBetween** function was used, as simple arithmetic can be used to calculate output rather than the system conducting date differentials at report run time. The **Show Value As** drop-down list has then been set to Hours Measure to achieve the desired output.

585

586

The second total, ‘Days to Departure (Exclusive)’ uses the **ToJulianDay** function:

587

588

ToJulianDay( <Date Expression> )

589

//Returns the Julian Day value of a date(time) expression. The Julian day is the continuous count of days since the beginning of the Julian Period and is used when comparing two dates.//

590

591

This works in the same way as the previous total, but is used for date fields rather than datetime fields.

592

593

The following calculation has been used:

594

595

ToJulianDay( Departure_Date )-ToJulianDay( Attendance_Date )

596

597

This returns an exclusive count of days between the two date values, resulting in a value of 0 should both dates match. Should an inclusive value be required, the system requires a date differential to be calculated at run time rather than a numeric calculation. This is achieved using the third total in this report.

598

599

To achieve an inclusive value for the third total, ‘Days to Departure (Inclusive)’, the following calculation logic has been used:

600

601

DaysBetween(Attendance_Date,Departure_Date

602

603

This will return an inclusive value, resulting in a value of 1 if both values match.

604

605

=== Calculating Age at the Time of an Appointment ===

606

607

Converting a date value to a numeric Julian Day value is especially useful when calculating date differentials in calculations, such as when calculating age at the time of an appointment. This can be achieved using the following calculation logic:

608

609

In Years:

610

FLOOR((ToJulianDay(<Appointment Date>) - ToJulianDay(<Date of Birth>))/365.25)

611

612

In Days:

613

FLOOR((ToJulianDay(<Appointment Date>) - ToJulianDay(<Date of Birth>~)~)~)

614

615

By converting both fields to Julian Day values, a simple subtraction will result in a value that can then be divided by 365.25 to derive age in years.

616

617

=== Order Confirmation Time ===

618

619

Converting a date time value to a numeric Julian Day Time value means that calculations can be used to derive time differentials between two fields. For example, the following calculations show the time between an order being received and confirmed:

620

621

In Seconds:

622

(ToJulianDayTime( Order_Confirmed_Time ) - ToJulianDayTime( Order_Created_Time ))

623

624

In Minutes:

625

ToJulianDayTime( Order_Confirmed_Time ) - ToJulianDayTime( Order_Created_Time))/60

626

627

= Extra Field Calculations =

628

629

//Please note that the following section applies to system administrators only.//

630

631

Supplementing Indexes with additional calculated fields allows administrators to provide pre-aggregated output that is calculated during build time. This can have an exponential impact on performance, significantly reducing report load times while also providing additional aggregations to end-users.

632

633

== Report Optimisation ==

634

635

When there is regular use of common logic across multiple reports, such as removing dummy records or defining cohorts of interest using queries, this filtering process can be optimised using calculated fields to generate Boolean flags.

636

637

This has some key advantages. First, the integrity of the query is controlled by administrators rather than relying on users to build complex queries. This reduces the margin for error and ensures reports are always run against the same cohort of records. Furthermore, when the report is loaded, the query bar does not contain a long string of individual values, freeing up this space for users to add their own queries.

638

639

For example, the following query is used in reporting to select a cohort of patients:

640

641

((Team_Code:("T1" OR "T2" OR "T3") AND Age:("0" OR "1" OR "2" OR "3" OR "4" OR "5" OR "6" OR "7" OR "8" OR "9" OR "10" OR "11" OR "12" OR "13" OR "14" OR "15" OR "16" OR "17" OR "18" OR "19")) AND Financial_Year:"2020-21")

642

643

While this does produce the report as expected, it can be optimised to instead use a Boolean flag as part of a calculated field. The above query can be translated to the simple CASE statement below:

644

645

CASE

646

WHEN In(TeamCode, 'T1', 'T2', 'T3')

647

&& Age < 20

648

&& FinancialYear = '2020-21'

THEN 1 ELSE 0

END

Reports can now reference the Boolean flag when referencing the cohort of patients, maintaining the integrity of the query and reducing load on the system when the report is run.

653

654

= {{id name="Functions"/}}Calculation Functions Index =

655

656

== Numeric Functions ==

=== Abs ===

Abs( <Expression> )

//A mathematical function that returns the absolute (positive) value of a numeric expression.//

662

663

**Arguments**

664

The expression has to be a numeric value.

665

666

**Examples**

667

Cost_Price = 0, Sale_Price = 1.69, Refund_Price = -1.69

Abs( Cost_Price )

would return 0

Abs( Sale_Price )

would return 1.69

Abs( Refund_Price )

would return 1.69

=== Ceil ===

Ceil( <Expression>, <Optional Decimal Places> )

681

//Returns a numeric value rounded up to the nearest whole number or to the specified number of decimal places.//

682

683

**Arguments**

684

The expression has to be a numeric value or numeric field.

685

686

**Examples**

687

Cost_Price = 1.49, Sale_Price = 1.69, Profit = 0.539

Ceil( Cost_Price )

would return 2.00

Ceil( Sale_Price )

would return 2.00

Ceil( Profit, 1 )

would return 0.6

=== Cos ===

Cos( <Expression> )

//Returns the cosine of the provided expression. The result is returned in radians NOT degrees.//

702

703

**Arguments**

704

The expression has to be a numeric value.

**Examples**

Value = 20

Would return 0.41

=== CountWorkingDays ===

711

712

CountWorkingDays( <Expression> , <Expression> )

713

//Counts the number of working days between two date fields. Please note that working days and holidays need to be configured by an administrator.//

714

715

**Examples**

716

Transaction Date = 19/09/2019, Return Date = 24/09/2019, Working Days = Monday, Tuesday, Wednesday, Thursday and Friday

717

718

CountWorkingDays( Transaction_Date , Return_Date )

would return 2

=== DateBetween ===

DateBetween( <Date Expression> , <Date Expression> , <TIMEPERIOD> )

724

//Returns the number of inclusive period values between two date expressions, except when a time is specified.//

725

726

**Arguments**

727

The expression must be a date(time) expression. A period must also be provided. This must be in UPPERCASE and can be one of the following: DAYS, WEEKS, MONTHS, YEARS, SECONDS, MINUTES OR HOURS.

728

729

**Examples**

730

Raised_Date = 09/12/2014 Closed_Date = 28/01/2015

731

732

DateBetween( Raised_Date , Closed_Date , DAYS )

733

would return 51

734

735

DateBetween( Raised_Date , Closed_Date , WEEKS )

736

would return 8

737

738

DateBetween( Raised_Date , Closed_Date , MONTHS )

739

would return 2

740

741

DateBetween( Raised_Date , Closed_Date , YEARS )

742

would return 1

743

744

DateBetween( Raised_Date , Closed_Date , SECONDS)

745

would return 4320001

746

747

DateBetween( Raised_Date , Closed_Date , MINUTES )

748

would return 72001

749

750

DateBetween( Raised_Date , Closed_Date , HOURS )

751

would return 1201

752

753

Please note that using **DateBetween(<>,<>, SECONDS)** over thousands of records will increase the processing time.

=== DaysBetween ===

DaysBetween( <Date Expression> , <Date Expression>, <Optional Day(s)> )

758

//Returns the number of inclusive period values between two date expressions.//

759

760

**Arguments**

761

The expression must be a date(time) expression. It can also contain optional arguments to exclude named days of the week from the calculation, commonly used to exclude weekends from a calculation.

762

763

**Examples**

764

Raised_Date = 15/01/2015 Closed_Date = 28/01/2015

765

766

DaysBetween( Raised_Date , Closed_Date )

767

would return 14

768

769

DaysBetween( Raised_Date , Closed_Date , SATURDAY, SUNDAY )

770

would return 10

771

772

This function is inclusive and returns ‘1’ if starting and finishing on the same day.

=== Fact ===

Fact( <Expression> )

//Returns the factorial of a given number.//

**Examples**

Fact(3)

would return 6

=== Floor ===

Floor( <Expression>, <Optional Decimal Places> )

786

Returns a numeric value rounded down to the nearest whole number //or to the specified number of decimal places.//.

787

788

**Arguments**

789

The expression has to be a numeric value or numeric field.

790

791

**Examples**

792

Cost_Price = 1.49, Sale_Price = 1.69, Profit = 0.579

Floor( Cost_Price )

would return 1.00

Floor( Sale_Price )

would return 1.00

Floor( Profit, 1 )

would return 0.5

=== GCD ===

GCD( <Expression> )

//Returns the greatest common divisor of two given numbers.//

807

808

**Arguments**

809

The expression has to be a numeric value.

**Examples**

GCD (8, 12)

would return 4

=== IsNull ===

IsNull( <Expression> , <Expression> )

818

//Returns a second specified value if the first is NULL.//

819

820

**Arguments**

821

The expression can be a string, date or numeric value.

**Example**

IsNull(Surname,'No name provided')

826

827

Would return the string value in the event the Surname value is NULL.

=== LCM ===

//Returns the lowest common multiple of two given numbers.//

832

833

**Arguments**

834

The expression has to be a numeric value.

**Examples**

LCM (3, 4)

would return 12

=== Len ===

Len( <String Expression> )

843

//Returns the number of characters of the specified string expression.//

844

845

**This function is only available for Calculated Fields in a Data Source Group or ETL stage.**

846

847

**Arguments**

848

The expression can be a string or numeric value.

849

850

**Examples**

851

Name = ‘John Smith’, Loyalty_Points = 691

Len( Name )

would return 10

Len( Loyalty_Points )

would return 3

=== Log ===

Log( <Expression> , <Expression> )

862

//Returns the logarithm of a number, using a supplied base.//

**Examples**

Log(64,2)

would return 6

=== Max ===

Max( <Expression> , <Expression> )

871

//Returns the maximum value of the provided expressions.//

872

873

**Arguments**

874

The expressions can be a numeric field name, a date field name, a numeric value or a date value. A combination of a field name and a constant value can be used.

875

876

**Examples**

877

Cost_Price = 3.99, Sale_Price = 4.99, Trans = 25/01/2015 Return = 27/01/2015

878

879

Max( Cost_Price, 1.99 )

880

would return 3.99

881

882

Max( Cost_Price, 5.99 )

883

would return 5.99

884

885

Max( Cost_Price, Sale_Price )

886

would return 5.99

887

888

Max( Trans, ToDate(2015,01,22) )

889

would return 25/01/2015 (20150125000000)

890

891

Max( Trans, Return )

892

would return 27/01/2015 (20150127000000)

=== Mod ===

Mod( <Expression> , <Expression> )

897

//Returns the remainder after division.//

**Examples**

Mod(10,4)

would return 2

=== Power ===

Power( <Expression> , <Expression> )

906

//Returns the sum of the provided power values.//

**Example**

Power(2,3)

would return 8, as 2*2*2=8

=== Quotient ===

Quotient( <Expression> , <Expression> )

915

//Returns the result of an integer division without the remainder.//

**Examples**

Quotient(12,5)

would return 2

=== Round ===

Round( <Expression>, <Optional Decimal Places> )

924

//Returns a numeric value rounded to the nearest whole number or to the specified number of decimal places..//

925

926

**Arguments**

927

The expression has to be a numeric value or numeric field.

928

929

**Examples**

930

Cost_Price = 1.49, Sale_Price = 1.69, Profit = 0.579

Round( Cost_Price )

would return 1.00

Round( Sale_Price )

would return 2.00

Round( Profit, 1 )

would return 0.6

=== Sign ===

Sign( <Expression> )

//Returns the sign of a number. If the number is positive, the function will return 1. If the number is negative, the function will return will return -1. If the number if 0, the function will return 0.///

**Examples**

Sign(22)

would return 1

=== Sin ===

Sin( <Expression> )

//Returns the sine of the provided expression. The result is returned in radians NOT degrees.//

954

955

**Arguments**

956

The expression has to be a numeric value.

**Examples**

Sin(30)

would return -0.99

=== Sqrt ===

Sqrt( <Expression> )

//Returns the square root of the provided expression.//

966

967

**Arguments**

968

The expression has to be a numeric value.

**Examples**

Sqrt(81)

would return 9

=== Tan ===

Tan( <Expression> )

//Returns the tangent of the provided expression. The result is returned in radians NOT degrees.//

978

979

**Arguments**

980

The expression has to be a numeric value.

**Example**

Tan(50)

would return 0.96

=== ToDay ===

ToDay( <Date Expression> )

989

//Returns the day date part of a date(time) expression.//

990

991

**Arguments**

992

The expression must be a date or datetime expression.

993

994

**Examples**

995

Transaction_Date = 19/09/2021

996

997

ToDay( Transaction_Date )

998

would return 19

999

1000

The numeric output of this function can then be used as part of other calculations.

=== ToDayOfWeek ===

ToDayOfWeek( <Date Expression> )

1005

//Returns the weekday number of a date(time) expression, where Sunday = 1.//

1006

1007

**Arguments**

1008

The expression must be a date or datetime expression.

1009

1010

**Examples**

1011

Transaction_Date = 07/04/2021 (Wednesday)

1012

1013

ToDayOfWeek( Transaction_Date )

Would return 4

=== ToHour ===

ToHour( <Date Expression> )

1019

//Returns the hour datepart of a datetime expression.//

1020

1021

**Arguments**

1022

The expression must be a date or datetime expression.

1023

1024

**Examples**

1025

Transaction_Date = 22/01/2015 18:32:15

1026

1027

ToHour( Transaction_Date )

would return 18

=== ToJulianDay ===

ToJulianDay( <Date Expression> )

1033

//Returns the Julian Day value of a date(time) expression. The Julian day is the continuous count of days since the beginning of the Julian Period and is used when comparing two dates.//

1034

1035

**Arguments**

1036

The expression must be a date or datetime expression.

1037

1038

**Examples**

1039

Raised_Date = 22/01/2015 Closed_Date = 25/01/2015

1040

1041

ToJulianDay( Raised_Date )

1042

would return 16457

1043

1044

ToJulianDay( Closed_Date )

1045

would return 16460

1046

1047

ToJulianDay( Closed_Date ) - ToJulianDay( Raised_Date )

1048

would return 3 (days)

1049

1050

=== ToJulianDayTime ===

1051

1052

ToJulianDayTime( <Date Expression> )

1053

//Returns the Julian Daytime value of a datetime expression. The Julian daytime is the continuous count of seconds since the beginning of the Julian Period and is used when comparing two datetimes.//

1054

1055

**Arguments**

1056

The expression must be a datetime expression.

1057

1058

**Examples**

1059

Raised_Date = 28/01/2015 10:49:51 Closed_Date = 28/01/2015 14:01:44

1060

1061

ToJulianDayTime( Raised_Date )

1062

would return 1422442191

1063

1064

ToJulianDayTime( Closed_Date )

1065

would return 1422453704

1066

1067

ToJulianDayTime( Closed_Date ) - ToJulianDayTime( Raised_Date )

1068

would return 11513 (seconds)

=== ToMinute ===

ToMinute( <Date Expression> )

1073

//Returns the minute datepart of a datetime expression.//

1074

1075

**Arguments**

1076

The expression must be a date or datetime expression.

1077

1078

**Examples **

1079

Transaction_Date = 22/01/2015 18:32:15

1080

1081

ToMinute( Transaction_Date )

would return 32

=== ToMonth ===

ToMonth( <Date Expression> )

1087

//Returns the month datepart of a date(time) expression.//

1088

1089

**Arguments**

1090

The expression must be a date or datetime expression.

1091

1092

**Examples**

1093

Transaction_Date = 22/01/2015

1094

1095

ToMonth( Transaction_Date )

would return 1

=== ToSecond ===

ToSecond( <Date Expression> )

1101

//Returns the second datepart of a datetime expression.//

1102

1103

**Arguments**

1104

The expression must be a date or datetime expression.

1105

1106

**Examples**

1107

Transaction_Date = 22/01/2015 18:32:15

1108

1109

ToSecond( Transaction_Date )

would return 15

=== ToNumber ===

ToNumber( <Expression> )

1115

//Converts string values into a number.//

1116

1117

**This function is only available for Calculated Fields in a Data Source Group or ETL stage.**

1118

1119

**Arguments**

1120

The expression must be a string value.

=== ToWeek ===

ToWeek( <Date Expression> )

1125

//Returns the week datepart of a date(time) expression.//

1126

1127

**Arguments**

1128

The expression must be a date or datetime expression.

1129

1130

**Examples**

1131

Transaction_Date = 22/01/2015

1132

1133

ToWeek( Transaction_Date )

would return 4

=== ToYear ===

ToYear( <Date Expression> )

1139

//Returns the year datepart of a date(time) expression.//

1140

1141

**Arguments**

1142

The expression must be a date or datetime expression.

1143

1144

**Examples**

1145

Transaction_Date = 22/01/2015

1146

1147

ToYear( Transaction_Date )

would return 2015

=== Random ===

Random( <Expression> )

1153

//Generates a random number between 0 and 1.//

=== Column ===

Column( <Expression> , <Expression> )

1158

//References the value of a total in a column.//

1159

1160

**This function is only available for Cell Calculations in Crosstabs.**

1161

1162

**Arguments**

1163

The first value is the position of the column, starting at 0 (zero) and the second value is the position of the total.

1164

1165

**Examples**

1166

Using this function in a Cell Calculations, it is possible to reference totals by their position rather than name. For example:

1167

1168

Column(0 ,1 ) + Column(1,1)

1169

1170

As 0 (zero) is the starting point, this calculation will add the second total of the first column to the second total of the second column.

1171

1172

**Reporting Examples**

1173

The following report displays a breakdown of credit card and debit card transactions:

1174

1175

[[image:CalcScreen11.png||height="255" width="600"]]

1176

1177

Using the Column function, totals can be referenced by their position rather than total name. The following cell calculation has been used to derive the ‘Total Cad Transactions’ total:

1178

1179

Column(0 ,0 ) + Column(0 ,1)

1180

1181

As only a single column value, 2019, has been added, the position 0 is referenced before referencing the first and second total, respectively.

1182

1183

=== ColumnTotal, RowTotal, GrandTotal ===

1184

1185

ColumnTotal( <Expression> )

1186

//References the column total, row total and grand total, respectively.//

1187

1188

These functions are only available for cell calculations.

1189

1190

**Reporting Examples**

1191

The following report displays a number of totals based on card transactions for each region:

1192

1193

[[image:CalcScreen12.png||height="214" width="600"]]

1194

1195

The first total, ‘Card Transactions’, simply counts all rows that contain ‘Credit Card’ or ‘Debit Card’ values for the MOP (Method of payment) column.

1196

1197

The second total, ‘Card Transactions (Running Total)’ uses the same logic as the previous total and has the **Show Value As** drop-down list set to Running Total Down Columns to provide a running total.

1198

1199

The third total, ‘Total Card Transactions’, uses a Cell calculation with the following logic:

ColumnTotal(1)

Using this function, the Column Total value can referenced to allow for the fourth total, ‘% Running Total’ to be calculated using the following logic:

1204

1205

('Card Transactions (Running Total)'/'Total Card Transactions')*100

1206

1207

This methodology is applicable for both the RowTotal and GrandTotal functions, respectively. By referencing these values in a report, they can be leveraged in future Cell calculations to achieve the desired output.

=== ColumnMin ===

Column( <Expression> )

1212

//References the minimum value of a total in a column.//

1213

1214

**This function is only available for Cell Calculations in Crosstabs.**

1215

1216

**Arguments**

1217

The expression should be the total from which you require the minimum value.

1218

1219

**Examples**

1220

A Crosstab has a Total called 'Sales'. To write out the minimum value from this total use the following syntax:

ColumnMin( 'Sales' )

[[image:Screenshot 2022-05-19 at 10.18.13.png||height="196" width="263"]]

=== ColumnMax ===

Column( <Expression> )

1229

//References the maximum value of a total in a column.//

1230

1231

**This function is only available for Cell Calculations in Crosstabs.**

1232

1233

**Arguments**

1234

The expression should be the total from which you require the maximum value.

1235

1236

**Examples**

1237

A Crosstab has a Total called 'Sales'. To write out the maximum value from this total use the following syntax:

ColumnMax( 'Sales' )

[[image:Screenshot 2022-05-19 at 10.25.54.png||height="197" width="266"]]

== Date Functions ==

**The following functions are available for Calculated Fields and Crosstab standard calculations (not cell calculations):**

1246

1247

=== FromJulianDay ===

1248

1249

FromJulianDay( <Date Expression> )

1250

//Returns a date as a numeric field from a Julian day. The Julian day is the continuous count of days since the beginning of the Julian Period and can be used when comparing two dates.//

1251

1252

**Examples**

1253

Raised_Date = 16457 Closed_Date = 16460

1254

1255

FromJulianDay( Raised_Date ) would return 20150122

1256

1257

FromJulianDay( Closed_Date ) would return 20150125

1258

1259

**Extra Field Example**

1260

The following example calculates the maximum difference between a referral date and appoint date on a row:

1261

1262

FromJulianDay( Max( ToJulianDay( Referral_Date ) , ToJulianDay( Appointment_Date ) ) )

1263

1264

Converting back to a date value from a Julian Day allows numeric calculations between dates to be achieved while retaining the date format in a single calculation.

1265

1266

=== FromJulianDayTime ===

1267

1268

FromJulianDayTime( <Date Expression> )

1269

//Returns a datetime expression from a Julian Daytime value. The Julian daytime is the continuous count of seconds since the beginning of the Julian Period and can be used when comparing two datetimes.//

1270

1271

**Example**

1272

Raised_Date = 1422442191 Closed_Date = 1422453704

1273

1274

FromJulianDayTime( Raised_Date )

1275

would return 20150128104951

1276

1277

FromJulianDayTime( Closed_Date )

1278

would return 20150128140144

1279

1280

Converting back to a date value from a Julian Day allows numeric calculations between dates to be achieved while retaining the date format in a single calculation.

1281

1282

**Extra Field Example**

1283

The following example shows an extra field calculation that outputs the maximum value between two date times fields on a row:

1284

1285

In seconds:

1286

FromJulianDayTime( Max( ToJulianDayTime( Referral_Date_Time ) , ToJulianDayTime( Appointment_Date_Time ) ) )

1287

1288

In minutes:

1289

FromJulianDayTime( Max( ToJulianDayTime( Referral_Date_Time ) , ToJulianDayTime( Appointment_Date_Time ) ) )/60

=== ToDate ===

ToDate( <Expression> , <Expression> , <Expression> )

1294

//Returns a date value.//

1295

1296

**Arguments**

1297

Each expression can either contain a date field or a numeric value.

1298

1299

The first expression contains the year element, the second expression contains the month element and the third expression contains the minute element.

1300

1301

**Examples**

1302

Raised_Date = 15/01/2015 Closed_Date = 16/01/2015

1303

1304

ToDate( 2014, 11, 8 )

1305

would return 08/11/2014

1306

1307

ToDate ( ToYear(Raised_Date), ToMonth(Raised_Date), ToDay(Raised_Date) )

1308

would return 15/01/2015

1309

1310

ToDate ( ToYear(Raised_Date), 12, 1 )

1311

would return 01/12/2015

1312

1313

ToDate ( ToYear(Raised_Date), 12, ToDay(Closed_Date) )

1314

would return 16/12/2015

1315

1316

**The output value will be a date field when created as a Calculated Field for an Index. For a Crosstab, the output value will actually be a numeric representation of the date. However, the value can still be used for calculations that require date comparisons/logic.**

=== ToDateTime ===

ToDate( <Expression> , <Expression> , <Expression>, <Expression> , <Expression> , <Expression> )

1321

//Returns a datetime value.//

1322

1323

**Arguments**

1324

Each expression can either contain a date field or a numeric value.

1325

1326

The first expression contains the year element, the second expression contains the month element and the third expression contains the minute element, the fourth expression contains the hour element, the fifth expression contains the minute element, the sixth expression contains the seconds element.

1327

1328

**Examples**

1329

Raised_Date = 15/01/2015 14:15:59 Closed_Date = 16/01/2015 17:11:23

1330

1331

ToDateTime( 2014, 11, 8, 23, 59, 59 )

1332

would return 08/11/2014 23:59:59

1333

1334

ToDateTime( ToYear(Raised_Date), ToMonth(Raised_Date), ToDay(Raised_Date), ToHour(Raised_Date), ToMinute(Raised_Date), ToSecond(Raised_Date) )

1335

would return 15/01/2015 14:15:59

1336

1337

ToDateTime( ToYear(Raised_Date), 12, 1, 23, 59, 59 )

1338

would return 01/12/2015 23:59:59

1339

1340

ToDateTime( ToYear(Raised_Date), 12, ToDay(Closed_Date), ToHour(Raised_Date), 45, ToSecond(Closed_Date) )

1341

would return 16/12/2015 14:45:23

1342

1343

**The output value will be a date field when created as a Calculated Field for an Index. For a Crosstab, the output value will actually be a numeric representation of the date. However, the value can still be used for calculations that require date comparisons/logic.**

1344

1345

== String Functions ==

1346

1347

**The following functions are available for Calculated Fields only (in Data Source Groups and ETL stage calculations).**

=== Format ===

Format( <Expression> , <Pattern> )

1352

//Changes the format of a field or string.//

1353

1354

**Arguments**

1355

The string expression can be a field or a static string value. This can be a date, a string or a numeric value. This function will only output a string value.

1356

1357

**Example**

1358

Expression : 01/01/2010

1359

1360

Format(Transaction_Date_Time, ‘dd/MM/yyyy hh:mm:ss’)

1361

would return 01/01/2010 00:00:00

=== IndexOf ===

IndexOf( <String Expression> , <Expression> , <Optional Start Point> )

1366

//Returns the position (numeric) of a specified character in a string expression.//

1367

1368

**Arguments**

1369

The string element can either be a field or a static string value. This is the string that will be parsed to look for an expression. The expression element specifies the character/string/value that is to be searched for. The expression element will accept a numeric value, a single string character or a strong expression.

1370

1371

There is also an optional expression for the start point of the search (in positional terms) e.g. Search for the first ‘&’ symbol that appears after the first ‘$’ symbol.

1372

1373

To include a special character in a search, prefix the desired character with the backslash character. For example, **\n** for a new line and **\t** for a tab space.

1374

1375

**Examples**

1376

Phrase = abc:123&456:Oscar:test

1377

1378

IndexOf( Phrase , ‘:’ )

1379

would return a value of 3 (a numeric value)

1380

1381

IndexOf( Phrase , ‘a’ )

1382

would return a value of 0

1383

1384

IndexOf( Phrase , ‘:’, 5 )

1385

would return a value of 11

1386

1387

IndexOf( Phrase , ‘&’, 10 )

1388

would return a value of -1

1389

1390

IndexOf( Phrase , ‘:’, IndexOf(Phrase, ‘&’) )

1391

would return a value of 11

1392

1393

The first position is 0 (zero). If the expression being searched for is not found, -1 will be returned.

=== LastIndexOf ===

LastIndexOf( <String Expression> , <Expression> , <Optional Start Point> )

1398

//Returns the last position (numeric) of a specified character in a string expression.//

1399

1400

**Arguments**

1401

The string element can either be a field or a static string value. This is the string that will be parsed to look for an expression. The expression element specifies the character/string/value that is to be searched for. The expression element will accept a numeric value, a single string character or a strong expression.

1402

1403

There is also an optional expression for the start point of the search (in positional terms) e.g. Search for the first ‘&’ symbol that appears after the first ‘$’ symbol.

1404

1405

**Examples**

1406

Phrase = abc:123&456:Oscar:test

1407

1408

LastIndexOf( Phrase , ‘:’ )

1409

would return a value of 17 (a numeric value)

1410

1411

LastIndexOf( Phrase , ‘a’ )

1412

would return a value of 15

1413

1414

LastIndexOf( Phrase , ‘:’, 5 )

1415

would return a value of 11

1416

1417

LastIndexOf( Phrase , ‘&’, 15 )

1418

would return a value of -1

1419

1420

LastIndexOf( Phrase , ‘:’, LastIndexOf(Phrase, ‘&’) )

1421

would return a value of 3

1422

1423

The first position is 0 (zero). If the expression being searched for is not found, -1 will be returned.

1424

1425

(% style="color:#444444; font-family:inherit; font-size:20px; letter-spacing:0.1px" %)LTrim

1426

1427

LTrim( <String Expression> , <Expression> )

1428

//Trims (removes) characters from left to right. Returns a string value.//

1429

1430

**Arguments**

1431

The string element can either be a field or a static string value. This is the string that will be trimmed. The expression element specifies how many characters of the string expression are to be trimmed (removed) from the left side of the string expression. The expression element will only accept a numeric value.

**Example**

Status = underway

LTrim( Status , 5 )

would return way

**Not to be confused with SQL Server TRIM function.**

=== LTrimSpaces ===

LTrimSpaces( <String Expression> )

1444

//Removes space padding to the left of a string.//

1445

1446

**Arguments**

1447

The string element can either be a field or a static string value. This is the string that will be trimmed.

**Examples**

Value = “ Hat“

would return “Hat”

=== Replace ===

Replace( <String Expression> , <String Expression> , <String Expression> )

1456

//Replaces part or all of a string with another string.//

1457

1458

**Arguments**

1459

The string expression can be a field or a static string value. This can be a date, a string or a numeric value.

1460

1461

**Example**

1462

Expression = Northern England and Ireland

1463

1464

Replace(Area_Name, ‘Northern England and Ireland’, ‘NE & I’)

Would return NE & I

=== RTrim ===

RTrim( <String Expression> , <Expression> )

1470

//Trims (removes) characters from right to left. Returns a string value.//

1471

1472

**Arguments**

1473

The string element can either be a field or a static string value. This is the string that will be trimmed. The expression element specifies how many characters of the string expression are to be trimmed (removed) from the right side of the string expression. The expression element will only accept a numeric value.

**Examples**

Status = underway

RTrim( Status , 3 )

would return under

**Not to be confused with SQL Server TRIM function.**

=== RtrimSpaces ===

RTrimSpaces( <String Expression> )

1486

//Removes space padding to the right of a string.//

1487

1488

**Arguments**

1489

The string element can either be a field or a static string value. This is the string that will be trimmed.

**Examples**

Value = “Hat “

would return “Hat”

=== Substring ===

Substring( <String Expression> , <Expression> , <Expression> )

1498

//Returns the specified part of a string expression.//

1499

1500

**Arguments**

1501

The string expression can be a field or a static string value. This can be a date, a string or a numeric value. The first expression is the start position of the substring. The second expression is the end position of the substring.

1502

1503

The start position of a string is 0 (zero).

1504

1505

The second expression is optional. Leaving this blank will return the remainder of the string expression after the specified start point.

1506

1507

**Examples**

1508

Phrase = price:3.99:cost:2.99

1509

1510

Substring(Phrase, 6)

1511

would return 3.99:cost:2.99

1512

1513

Substring(Phrase, 0, 5)

1514

would return price

1515

1516

In combination with IndexOf() the following can be used:

1517

Substring(Phrase, 0, IndexOf(Phrase, ‘:’))

1518

would return price

1519

1520

Substring(Phrase, LastIndexOf(Phrase, ‘:’))

1521

would return 2.99

1522

1523

Substring(Phrase, IndexOf(Phrase, ‘:’)+1,IndexOf(Phrase,’:’,IndexOf(Phrase,’:’)+1))

would return 3.99

=== ToLower ===

ToLower( <String Expression> )

1529

//Returns the provided expression in lower case.//

1530

1531

**Arguments**

1532

The string element can either be a field or a static string value.

**Examples**

Value = “Hat“

Would return “hat”

=== ToString ===

ToString( <Expression> )

1541

//Returns the provided expression as a string.//

1542

1543

**Arguments**

1544

The string element can either be a field or a static numeric or date value.

**Examples**

Expression = 30.9

Would return 30.9 as a string

=== ToUpper ===

ToUpper( <String Expression> )

1553

//Returns the provided expression in upper case.//

1554

1555

**Arguments**

1556

The string element can either be a field or a static string value.

**Examples**

Value = “Hat“

Would return “HAT”

== Boolean Functions ==

=== Contains ===

Contains( <Expression> , <Expression> )

1567

//A function used to search for a value.//

1568

1569

To include a special character in a search, prefix the desired character with the backslash character. For example, **\n** for a new line and **\t** for a tab space.

1570

1571

**Available for use in Calculated Fields in a Data Source Group or an ETL stage calculation.**

1572

1573

**Arguments**

1574

Can only be used as part of a CASE or IF statement as part of a calculated field.

1575

1576

**Examples**

1577

CASE WHEN Contains( MOP , 'Cash' ) THEN 1 ELSE 0 END

1578

would return an extra field containing 1 where MOP contains Cash, or 0

=== In ===

In ( <Expression> , <Expression> )

1583

Determines whether a specific value appears in a field.

1584

1585

**Available for use in both Crosstabs and Calculated Fields in Data Source Group and ETL stage calculation.**

1586

1587

**Arguments**

1588

The first expression is the field of interest and the second expression is the value(s) searched for.

1589

1590

**Examples**

1591

CASE WHEN In( Day_of_Week , 2, 3, 4) THEN 1 ELSE 0 END

1592

would count records where Day_of_Week = 2,3 or 4

1593

1594

CASE WHEN In( MOP , 'Cash' , 'Vouchers') THEN 1 ELSE 0 END

1595

Would count records where cash or vouchers were used as the method of payment.

=== IsFormat ===

IsFormat( <String Expression> , <Pattern>)

1600

//Returns ‘True’ or ‘False’ on whether a field/string is in a specified format.//

1601

1602

**Available for use in Calculated Fields in a Data Source Group or an ETL stage calculation.**

1603

1604

**Arguments**

1605

The string expression can be a field or a static string value. This can be a date, a string or a numeric value.

1606

1607

**Examples**

1608

Expression: 10/05/2016 15:10:55

1609

1610

IF IsFormat(Transaction_Date_Time, ‘dd/MM/yyyy hh:mm:ss’ THEN {1} ELSE {0}

would return 1

=== Matches ===

Matches( <Expression> , <Expression> )

1616

//Used within a CASE or IF statement, returns whether a field matches a specified value.//

1617

1618

**Available for use in Calculated Fields in a Data Source Group or an ETL stage calculation.**

1619

1620

**Arguments**

1621

The string expression can be a field or a static string value. This can be a date, a string or a numeric value.

**Example**

Expression = Male

CASE WHEN Matches( Gender , 'Male' ) THEN 1 ELSE 0 END

would return 1

=== Within ===

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

1632

or

1633

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

1634

//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.//

1635

1636

**Arguments**

1637

The period refers to the period of time to be used by the range expression. This argument can contain DAYS, WEEKS, MONTHS, YEARS.

1638

1639

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

1640

1641

The second date expression is the end point of the required date range. For example, to return all data for the previous month, the end point of the range would be:

1642

1643

FIRST_DAY_OF_MONTH-1DAY

1644

1645

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

1646

1647

Additional Date Expressions can be used if only elements of a date are available as separate fields.

1648

1649

In this case, the structure of the arguments is as follows:

1650

1651

• Period (WEEKS, MONTHS, YEARS)

1652

• Date Expression (1st date element)

1653

• Date Expression (2nd date element)

1654

• Date Expression (1st date element end point)

1655

• Date Expression (2nd date element end point)

1656

• Expression (range value of the Within function)

1657

1658

**Examples**

1659

Within( WEEKS , Sales_Date , LAST_DAY_OF_WEEK-1WEEK , 0)

1660

would return all rows that have the Sales Date that exists in the previous week

1661

1662

Usage would be:

1663

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

1664

Within( WEEKS , Sales_Date , LAST_DAY_OF_WEEK-1WEEK , 3)

1665

would return all rows that have the Sales Date that exists in the previous 4 weeks

1666

1667

Usage would be:

1668

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

1669

Within( WEEKS , Sales_Date_Week , Sales_Date_Year, LAST_DAY_OF_WEEK-1WEEK, LAST_DAY_OF_WEEK-1WEEK, 0)

1670

would return all rows that have the Sales Date that exists in the previous week

=== IsColumn ===

IsColumn( <String Expression> )

1675

//References a column or nested column.//

1676

1677

This function is only available for Crosstabs.

1678

1679

**Arguments**

1680

The string expression should be a case-sensitive reference to the name of a column value. The reference must be contained in single quotes. To reference a nested column value, use comma separated string values.

1681

1682

**Reporting Examples**

1683

In this report, two fields have been displayed on the column axis, with Area Name nested below Region Name:

1684

1685

[[image:CalcScreen13.png||height="113" width="550"]]

1686

1687

This report has been constructed using the **IsColumn** function so that the column outputs can be individually specified:

1688

1689

CASE

1690

WHEN IsColumn('London')

THEN Price_EX_VAT

ELSE Prince_INC_VAT

END

For any values in the London column, the ‘Price_EX_VAT’ field is summed. For every other column, the ‘Price_INC_VAT’ field is summed.

1696

1697

Nested values can also be referenced by comma separating the string values. For example:

1698

1699

CASE

1700

WHEN IsColumn('London') THEN Price_EX_VAT

1701

WHEN IsColumn ('Midlands and East', 'East Anglia) THEN Price_EX_VAT

ELSE Prince_INC_VAT

END

In this next report, multiple outputs are specified in the calculation so that different totals can be specified for different columns:

1706

1707

[[image:CalcScreen15.png||height="173" width="400"]]

1708

1709

First, a ‘Costs’ total has been created by summing the ‘Cost Price’ field. This total is then hidden from the output using the **Hide Total in Table** option.

1710

1711

A cell calculation referencing this total has then been created using the following logic:

1712

1713

CASE

1714

WHEN IsColumn('London') THEN 'Costs'*1.4

1715

WHEN IsColumn('Midlands and East') THEN 'Costs'*1.2

ELSE 'Costs'*1.1

END

Two columns have now been specified. Should any additional columns be added to the report, their totals will be calculated using the ‘ELSE’ figure.

=== IsRow ===

IsRow( <String Expression> )

1724

//References a row or nested row.//

1725

1726

This function is only available for Crosstabs.

1727

1728

**Arguments**

1729

The string expression should be a case-sensitive reference to the name of a row value. The reference must be contained in single quotes. To reference a nested row value use comma separated string values.

1730

1731

**Examples**

1732

Much like the **IsColumn** function, using **IsRow** allows users to specify the output for specific areas of the report, this time at row level.

1733

1734

The following report is a transposed version of the example used for the **IsColumn** function, with the Region Name and Area Name fields now added to the row:

1735

1736

[[image:CalcScreen15.png||height="173" width="400"]]

1737

1738

The calculation has been tweaked to use the IsRow function to account for the axis change:

CASE

WHEN IsRow('London')

THEN Price_EX_VAT

ELSE Prince_INC_VAT

END

Nested values can also be referenced by comma separating the string values:

1747

1748

CASE

1749

WHEN IsRow('London') THEN Price_EX_VAT

1750

WHEN IsRow ('Midlands and East', 'East Midlands') THEN Price_EX_VAT

ELSE Prince_INC_VAT

END

=== IsColumnTotal, IsGrandTotal, IsRowTotal, IsSubTotal ===

1755

1756

IsColumnTotal(), IsGrandTotal(), IsRowTotal(), IsSubTotal()

1757

References a column total or nested column total, a grand total, a row total or nested row total and a subtotal, respectively.

1758

1759

**Arguments**

1760

The string expression should be a case-sensitive reference to the name of a row value. The reference must be contained in single quotes. To reference a nested row value use comma separated string values.

1761

1762

**Reporting Example**

1763

The following report has been constructed to omit a percentage value from the subtotal output:

1764

1765

[[image:CalcScreen16.png||height="256" width="550"]]

1766

1767

Using the following cell calculation, the ‘% Non-Branded Items’ total has been controlled to only be printed at column total, row total and grand total level:

1768

1769

CASE

1770

WHEN IsColumnTotal() THEN (('Non-Branded Sales'/'Total Transactions')*100)

1771

WHEN IsRowTotal() THEN (('Non-Branded Sales'/'Total Transactions')*100)

1772

WHEN IsGrandTotal() THEN (('Non-Branded Sales'/'Total Transactions')*100)

1773

WHEN IsSubTotal() THEN NULL

1774

END

1775

1776

The **IsColumnTotal**, **IsRowTotal** and **IsGrandTotal** functions have been used to specify the output of the percentage calculation. By specifying a NULL value for the **IsSubTotal** function, the total can be omitted from the sub total output.

1777

1778

== Aggregation Functions ==

1779

1780

The following functions are available for Crosstabs only, unless otherwise specified, and produce an aggregated value.

1781

1782

=== CountDistinct ===

1783

1784

CountDistinct( <Expression>, <String Expression> )

1785

//Returns a distinct count based on the value in the expression and the dynamic view being referenced.//

1786

1787

**Arguments**

1788

The expression output should be a numeric field.

1789

1790

**Examples**

1791

To return the number of employees who have handled a cash transaction:

1792

CountDistinct( CASE WHEN MOP = 'Cash' THEN 1 END , 'Employees' )

1793

1794

It is important to not return anything other than 1 in the CASE logic as the CountDistinct will also count the employees who did not perform Cash transactions i.e. CountDistinct( CASE WHEN MOP = 'Cash' THEN 1 ELSE 0 END , 'Employees' ) will count the distinct employees returning 1 **and** the distinct employees returning 0.

=== Average ===

Average( <Expression> )

1799

//Returns the average of all values in the specified expression (field). This only returns the average of fields that do not contain NULLs.//

1800

1801

**This option is only available when using the Row Total Total Type.**

1802

1803

**Arguments**

1804

The expression should be a numeric field.

**Examples**

Average(Total_Value)

=== AverageAll ===

AverageAll( <Expression> )

1812

//Returns the average of all values in the specified expression (field). This returns the average of ALL fields including those that contain NULLs.//

1813

1814

**This option is only available when using the Row Total Total Type.**

1815

1816

**Arguments**

1817

The expression should be a numeric field.

1818

1819

**Examples**

1820

AverageAll(Total_Value)

1821

1822

=== PercentileSum ===

1823

1824

PercentileSum(<Expression> ,<Expression> ,<Expression> )

1825

//Returns the sum of n% of a specified expression (field).//

1826

1827

**Arguments**

1828

The first expression should be a numeric field, the second expression should be numeric and is the start point, in %, of the range to be used, and the third expression should be numeric and is the end point, in %, of the range to be used.

1829

1830

**Examples**

1831

To return the sum of the top 10% of values:

1832

PercentileSum(Sale_Price, 90, 100)

1833

1834

To return the sum of the bottom 25% of values:

1835

PercentileSum(Sale_Price, 0, 25)

1836

1837

=== PercentileValue ===

1838

1839

PercentileValue(<Expression> ,<Expression> )

1840

//Returns the percentile value of the specified expression (field). This is the value below which a given percentage of observations in a group of observations falls. For example, the 20th percentile is the value below which 20% of the observations may be found.//

1841

1842

**Arguments**

1843

The first expression should be a numeric field and the second expression should be a numeric value that specifies the percentile value to be used by the function.

1844

1845

**Examples**

1846

To return the limit of the lower quartile (bottom 25%) of values:

1847

PercentileValue(Sale_Price, 25)

1848

1849

To return the limit of the median (bottom 50%) of values:

1850

PercentileValue(Sale_Price, 50)

1851

1852

=== PercentileAverage ===

1853

1854

PercentileAverage(<Expression> ,<Expression> ,<Expression>)

1855

//Returns the average of the values that are between a percentile range given for the specified expression (field).//

1856

1857

**Arguments**

1858

The first expression should be a numeric field, the second expression should be a numeric value that specifies the lower percentile value to be used by the function and the third expression should be a numeric value that specifies the upper percentile value to be used.

1859

1860

**Examples**

1861

To return the average of the lower quartile (bottom 25%) of values:

1862

PercentileAverage(Sale_Price, 0, 25)

1863

1864

To return the average of the values between the 25th percentile and the 50th percentile:

1865

PercentileAverage(Sale_Price, 25, 50)

1866

1867

=== PercentileCount ===

1868

1869

PercentileSum (<Expression> ,<Expression> ,<Expression>)

1870

//Returns the number of values that are between a percentile range given for the specified expression (field).//

1871

1872

**Arguments**

1873

The first expression should be a numeric field, the second expression should be a numeric value that specifies the lower percentile value to be used by the function and the third expression should be a numeric value that specifies the upper percentile value to be used.

1874

1875

**Examples**

1876

To return the number of values in the lower quartile (bottom 25%) of values:

1877

PercentileCount(Sale_Price, 0, 25)

1878

1879

To return the number of values between the 25th percentile and the 50th percentile:

1880

PercentileCount(Sale_Price, 25, 50)

1881

1882

=== PercentileMin ===

1883

1884

PercentileMin (<Expression> ,<Expression> ,<Expression>)

1885

//Returns the Minimum data value within a percentile range given for the specified expression (field).//

1886

1887

**Arguments**

1888

The first expression should be a numeric field, the second expression should be a numeric value that specifies the lower percentile value to be used by the function and the third expression should be a numeric value that specifies the upper percentile value to be used.

1889

1890

**Examples**

1891

To return the Minimum data value of the lower quartile (bottom 25%) of values:

1892

PercentileMin(Sale_Price, 0, 25)

1893

1894

To return the Minimum data value between the 25th percentile and the 50th percentile:

1895

PercentileMin(Sale_Price, 25, 50)

1896

1897

=== PercentileMax ===

1898

1899

PercentileMax (<Expression> ,<Expression> ,<Expression>)

1900

//Returns the Maximum data value within a percentile range given for the specified expression (field).//

1901

1902

**Arguments**

1903

The first expression should be a numeric field, the second expression should be a numeric value that specifies the lower percentile value to be used by the function and the third expression should be a numeric value that specifies the upper percentile value to be used.

1904

1905

**Examples**

1906

To return the Maximum data value of the lower quartile (bottom 25%) of values:

1907

PercentileMax(Sale_Price, 0, 25)

1908

1909

To return the Maximum data value between the 25th percentile and the 50th percentile:

1910

PercentileMax(Sale_Price, 25, 50)

=== Sum ===

Sum( <Expression> )

//Returns the sum of the values in the specified expression (field).//

1916

1917

**This option is only available when using the RowTotal total type.**

1918

1919

**Arguments**

1920

The expression should be a numeric field.

**Examples**

Sum(Total_Value)

7.5.9 CountDistinct / SumDistinct

1926

CountDistinct( <Expression> , <String Expression> )

1927

//Returns the distinct count from a dynamic view following the specified calculation logic.//

1928

1929

SumDistinct( <Expression> , <String Expression> )

1930

//Sums the distinct count from a dynamic view following the specified calculation logic.//

1931

1932

**Arguments**

1933

The expression represents the calculation logic and the string expression is the name of the dynamic view in single quotes. Dynamic Views can be selected from the **Values** drop-down list.

1934

1935

When using the Dynamic View drop-down list functionality in a standard total, the distinct count is run against the records before any calculation logic is applied. In some circumstances, this may remove records of interest. By using either calculation, the distinct count is run against the records after the calculation logic has been applied.

=== SumDistinct ===

SumDistinct( <Expression>, <String Expression> )

1940

//Returns a distinct sum based on the value in the expression and the dynamic view being referenced.//

1941

1942

**Arguments**

1943

The expression output should be a numeric field.

1944

1945

**Examples**

1946

To return the number of employees who have handled a cash transaction:

1947

SumDistinct( CASE WHEN MOP = 'Cash' THEN Line_Price END , 'Employees' )

=== Max ===

Max( <Expression>, <Expression>, TRUE )

1952

//Returns the maximum value of two aggregations (from the same or different totals).//

1953

1954

**Arguments**

1955

The expressions should be a numeric values. They need to refer to the runtime cell position (starting position being 0) of an aggregated value.

1956

1957

**Examples**

1958

To return the maximum sales value of the years 2021 (position 3) and 2022 (position 4) in the Crosstab below, you would use the following syntax:

1959

Max( 3 , 4 , TRUE )

1960

1961

[[image:Screenshot 2022-05-19 at 12.44.30.png||height="173" width="549"]]

=== Min ===

Min( <Expression>, <Expression>, TRUE )

1966

//Returns the minimum value of two aggregations (from the same or different totals).//

1967

1968

**Arguments**

1969

The expressions should be a numeric values. They need to refer to the runtime cell position (starting position being 0) of an aggregated value.

1970

1971

**Examples**

1972

To return the minimum sales value of the years 2021 (position 3) and 2022 (position 4) in the Crosstab below, you would use the following syntax:

1973

Min( 3 , 4 , TRUE )

1974

1975

[[image:Screenshot 2022-05-19 at 12.41.04.png||height="173" width="549"]]

1976

1977

== Miscellaneous Functions ==

=== FORMFIELD ===

The following function is available for Crosstabs only and allow users to reference a filter, or elements of a filter, rather than allowing the filter to filter the data in the report.

1982

1983

FORMFIELD^<Identifier>

1984

//Returns the value of a filter.//

1985

1986

**Arguments**

1987

The expression should be the same as the data type of the filter. Filters must be referenced using double quotes (").

1988

1989

**Examples**

1990

CASE WHEN ToMonth( Transaction_Month ) = ToMonth( FORMFIELD^"Transaction Month" ) && ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) THEN Line_Price END

1991

1992

By enabling the Ignore option for the Transaction Month filter, changing its value will only affect this single total in the report.

1993

1994

To reference the higher and lower values from a range filter, enter ^HIGH and ^LOW, respectively, after the filter name. For example:

1995

1996

CASE WHEN Transaction_Date BETWEEN FORMFIELD^"Transaction Date"^LOW AND FORMFIELD^"Transaction Date"^HIGH THEN Cost_Price END

1997

1998

To reference FORMFIELD values in a multi-select drop list use the In() function. For example:

1999

2000

CASE WHEN In(FORMFIELD^"Product Group",Product_Group) THEN 1 END

2001

2002

For more FORMFIELD examples, please refer to the [[Crosstab Reporting Guide>>doc:Technical Documentation.CXAIR.Reporting Guides.09a\. Crosstab Reporting Guide.WebHome]].

2003

2004

=== Cell Referencing ===

2005

2006

The following syntax is available for Cell/Total Calculations only and allow users to reference a cell in a Crosstab.

2007

2008

Sum(rows(<Row Name>),columns(<Column Name>),<Total Position>)

2009

//Returns the value of a cell.//

2010

2011

**Arguments**

2012

Note: this option is not available in the Functions drop list.

2013

2014

The syntax has to start with **Sum**. You can either reference a Row or a Column or both. Totals start at position 0 (zero).

2015

2016

**Example**

2017

For a Crosstab with Provinces as rows and Customer Segments as columns with 2 totals (Count and Sales). I can references the Ontario Corporate Sales by using:

2018

2019

Sum(rows('Ontario'),columns('Corporate'),1)

2020

2021

== String Calculation ==

2022

2023

Allows a custom phrase to be displayed in a report and aggregation tooltips.

2024

2025

**Arguments**

2026

The phrase must be punctuated using double quotes (").

2027

2028

**Examples**

2029

CASE WHEN 'Total' > 100 THEN "Check Source Data" END

2030

2031

To utilise String Calculations as part of an aggregation tooltip, navigate to the Tooltips tab of a total and click the New Tooltip button under the Aggregation Tooltips heading and select the relevant String Calculation total.

2032

2033

The following example utilises four totals: a count of transactions, % branded products sold, row count, average amount of branded items sold and the following String Calculation:

2034

2035

CASE

2036

WHEN Transactions < 100000 && '% Branded Products' < 'Branded Average'

2037

THEN 'Transactions' + " is quite a low value and the % branded sales (" +'% Branded Products' + ") is lower than the average of " + 'Branded Average'

2038

END

2039

2040

By referencing totals as part of the string values, the tooltip will display the hard-coded text alongside the dynamic aggregation values.

2041

2042

For more information regarding the make-up of this report, please refer to the [[Crosstab Reporting Guide>>doc:Technical Documentation.CXAIR.Reporting Guides.09a\. Crosstab Reporting Guide.WebHome]], where a detailed breakdown is available.

Wiki source code of 03ci. Calculation Builder

Navigation