The CALC function is a powerful tool made available to you in Tricentis Tosca. But how can you use it practically? In this article you will learn how to use CALC (even with buffers) for executing both elementary and a many other additional functions.
|
Please note that this article refers to the CALC function for classic engines. For information on the corresponding TBox functionality, see chapter "Perform calculations". |
Syntax and structure
The syntax for using CALC is:
|
Syntax: |
{CALC[<modified Excel String>]} |
This modified Excel string corresponds to an Excel entry without the leading equals sign. The following example shows what a simple calculation should look like:
|
{CALC[5+5]} |
In the next step we want to introduce a buffer. Let's assume that the buffer fLengthInch has a length entered in inches, and you need to have the length in centimeters. In order to achieve this, you need to multiply the value by 2.54. Simply place the buffer as a variable instead of the number as shown in the example below.
|
|
{CALC[{B[fLengthInch]}*2,54]} |
It is best to first create the formulas in Excel and then copy them into an editor. We can then replace the cell address there with the buffer before it is copied into Tosca Commander™. In order to evaluate your formula, simply select the value in the Value column and execute it using Translate Value in the context menu. The syntax of the entry is checked to ensure it is correct and to prevent unpleasant surprises occurring during the test.
Beyond basic calculations
Let's assume there is a buffer sFullName which contains a string <FirstName LastName> .
In order to find the last word, you would enter
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
in Excel. The value Tosca Commander is located in the cell A1.
You would need to search the string from the right until the space. You obtain the number of characters from the length of the string minus the search result for the space (here being searched from the left). In Tosca this would be:
{CALC[
RIGHT("{B[sFullName]}"
;
LEN("{B[sFullName]}")
-FIND(" ";"{B[sFullName]}")
)
]}
Please be aware of an important detail here: Excel expects a string, thus our buffer variable must either be a string or be enclosed in quotation marks.
If you use the correct syntax here you will obtain the result Commander and you can continue working with this value.
You may ask at this point: Does this also work with test data management? Yes, it does! For test data management, instead of the buffer syntax, you only need to enter {TDM[sFullName]}.
Replacing individual characters
One common area of interest is learning how to manipulate the syntax of floating point numbers. In German, for instance, a comma is used to separate the ones and the decimal places. In American formatting, this comma must be replaced by a period. This can get interesting when this operation is performed with a thousands separator.
Let's take a value such as 3.141,59. For a valid input in the American version of the user interface of the SUT, we need to replace the comma by a period and remove the thousands separator.
|
|
{CALC[SUBSTITUTE(SUBSTITUTE("3.141,59",".",""),",",".")]} |
Converting date expressions
Converting date values into different formats has become an almost routine problem in large companies. Probably the most prevalent example is the difference between American and European date expressions mm/dd/yyyy and dd.mm.yyyy.
Let's take for our example the date in the form of 18 October 2012 which has to be formatted for an input box as follows: 10/18/2012.
We do this in Excel by using the command TEXT.
|
|
{CALC[TEXT("18 October 2012","mm/dd/yyyy")]} |
Escaping special character functions in formulas
If you work with the text function for some time, you will soon encounter the problem with special character functions. These can easily cause an error or an unexpected result when one of your formatting characters is used.
To continue using these values, insert a leading backslash. For practical purposes, we recommend creating these formulas in Excel first.
|
|
{CALC[TEXT(NOW(),"\Mo\nt\h mmmm")]} |
This calc line results in Month October. An error is returned if all the backslashes are removed. If a backslash is only placed in front of the n, the string {CALC[TEXT(NOW(),"Mo\nth MMMM")]} returns the seemingly cryptic result of 10ont18 October. This is the correct output in Excel, but if you really want to play it safe, simply mask each character with a backslash.
Using date formats with various languages
If the TEXT function is used with CALC, a language different from the one that is set, can be used for formatting.
For this purpose, the language code (LCID Hex) can be added to the formula in square brackets.
|
{CALC[TEXT("01.01.2012";"JJJJ. |
2012.January 01 |
|
{CALC[TEXT("01.03.2012";"[$-409]MMMM")]} |
March |
|
{CALC[TEXT("01. Oktober 2012";"TT. |
01. Oct 2012 |
The language code is specified using square brackets in the formatting string and has a leading dollar sign and a hyphen. The subsequent codes are hexadecimal entries. You need not specify the prefix 0.
The examples mentioned above show a translation from German into English. In addition, you can also use the following values:
0C09 English (Australian)
1009 English (Canadian)
0809 English (U.K.)
0409 English (U.S.)
0407 German
0C07 German (Austrian)
0807 German (Swiss)
Additional useful Excel functions
TRIM: Removes all unnecessary spaces from strings. The entry {CALC[TRIM("Passion for Testing")]} for instance, would result in Passion for Testing.
CLEAN: Removes all non-printable commands and characters from strings. This can help optimize a string, especially in combination with TRIM.
ROUND: Here you can round off a specified number of digits (with negative number entry). Entering {CALC[ROUND(12345;-2)]} for example, would return 12300.
RANDBETWEEN: If you need to generate a random number you can enter this function and specify the top and bottom numbers. Example: {CALC[RANDBETWEEN(1;10)]}
You can also use functions with several arguments such as AVERAGE and SUM . It would then look like this with buffers: {CALC[AVERAGE({B[value1]};{B[value2]};{B[value3]})]}.
Checklist for using CALC
|
|
Are all the brackets placed correctly? |
|
|
Are all buffers which represent strings enclosed in quotation marks? |
|
|
Are the formulas free from unnecessary spaces or line breaks? |
|
|
Is the setting for Excel localization set to YES? Then the settings needed for using an English or German Excel version are in effect. If you work in a multinational company with a German Excel version and are creating test cases for colleagues, please clarify with them if you should turn off the localization. The main difference between the English (non-localized) and German Excel (besides the different command names), is that the German arguments and functions are separated by a semicolon and the period is a comma. =FIND("3","123.45") =SUCHEN("3";"123,45") |
Information about this topic in the online Help
Setting for Excel localization
Calc und other dynamic expressions
Subset download: Re-entering everything is not necessary
The subset Article_CALC.tce containing the mentioned TestCases can be downloaded from the Tosca Support Portal.
Before you can evaluate values using Translate Values, you need to fill the buffers with the TestStepValues from the folder Buffer by running the TestCase Buffer in Scratchbook using F6.