This site requires JavaScript to be enabled
IE BUMPER

Logo

Dear Tricentis qTest users,

If you would like to submit a Tricentis qTest ticket,

please use this support request form.

Tosca Version
IncidentLookup using list
Language
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
  Help
IE BUMPER
Categories
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
AND
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Wildcards
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Phrases
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

CALC compendium

This article shows various examples on how to use Excel formulas in Tosca by using CALC


Situation

Important!

If your Calc expression doesn´t work in Excel it won´t either do in Tosca. 

Please note that depending on your localization settings characters like ";" have to be replaced with "," -UMSCHREIBEN

"," is commonly used on machines with english "Region and Language" Windows settings

DATUM MUSS AUF ENGLISCH GEÄNDERT WERDEN: TT  -> DD- JJJJ-> YYYY

Solution

1.) Comparing dates including the dynamic expression {Date}, which outputs the current Date and a Buffer {B[your_date_buffer]} that has been set to: 25.06.2014

{CALC[AND(DATEVALUE("{Date}")>DATEVALUE("{B[your_date_buffer]}");DATEVALUE("{Date}")<=DATEVALUE("03.09.2016"))]}

Output is either True or False

 

2.) In this case a thousands separator should be used. It also shows how to manually set the decimal places used

{CALC[FIXED(10000000,00+1,99;3; FALSE)]} - False do not prevents Commas, so the result will be: "10.000.001,990"

{CALC[FIXED(10000000+1,99;2; TRUE)]} - True prevents Commas, so the result will be "10000001,99"

{CALC[FIXED(10000000+1,99;1; TRUE)]} - Now the result gets rounded to "10000002,0"

Depending on your language settings, respectively your localization settings within Tosca, you will either get "10.000.002,99" or "10,000,002.99" as a result.

 

3.) A value from a buffer should be trimmed by using Excel´s LEFT respectively RIGHT formula. The value of your sample buffer is "12345678"

{CALC[LEFT("{B[sample]}"; 3)]} - the result will be "123"

{CALC[RIGHT("{B[sample]}"; 5)]} - the result will be "45678"

 

4.) A calculation should be performed where the buffered values have thousands separators. Quotation marks are required to perform this calculation.

Buffer1 = 3.000

Buffer2 = 1.000

Buffer3 = 2

{CALC["{B[Buffer1]}"-"{B[Buffer2]}"*"{B[Buffer3]}"]} - the result will be "1000" without thousands separator. 

For a solution with thousands separators please see the solution below:

{CALC[FIXED("{B[Buffer1]}"-"{B[Buffer2]}"*"{B[Buffer3]}";0; FALSE)]} - the result will be "1.000"

 

5.) All decimal places including the comma should be removed

{CALC[LEFT("10000,00";FIND(",";"10000,00")-1)]} - the result will be "10000"

 

6.) Various date conversions should be performed

{CALC[TEXT(NOW();"TT/MM/JJ")]} - results in the current date formatted as "TT/MM/JJ" (dynamic) 

{CALC[TEXT("{B[Buffer1]}";"JJJJ/MM/TT")]} - here a buffer was used: "2015/04/13"

{CALC[TEXT("2015.04.12";"JJJJ/M/T")]} - results in "2015/4/12"

{CALC[TEXT("12.04.15";"MM/JJ/T")]} - results in "04/15/12"

{CALC[TEXT("02.04.15";"M.TT.JJJJ")]} - results in "4.02.2015"

 

Additionally Locale IDs (LCID Hex) can be added to the formula to translate given dates into various formats

Be sure to add the LCID Hex code by using squared brackets:

{CALC[TEXT("12.04.2015";"TTTT [$-0409]MMMM JJJJ")]} - "Sunday April 2015"

{CALC[TEXT("01.03.2015";"[$-0409]MMMM")]} - "March"

{CALC[TEXT("01. Oktober 2012";"TT.[$-0409]MMM.JJJJ")]} - "01.Oct.2012"

A list of the most often Locale IDs:

0C09        English (Australian)
1009         English (Canadian)
0809         English (U.K.)
0409         English (U.S.)
0407         German
0C07        German (Austrian)
0807         German (Swiss)

The full list can be found here

 

Some random string should be written in captial letters:

 {CALC[UPPER("{RANDOMTEXT[]}")]}

 

If condition in calc

 

 

 

 

 

 

Reference

Link to Microsoft´s Excel support 

0 0

Arpit Patel

Created: 2017-08-09 10:07:26

Would calculation work if MS Excel is not installed in the computer ?

Martin Karolyi

2017-08-09 15:50:14

No, Excel has to be installed

IE BUMPER

Tosca is the perfect solution

Optimize - Manage - Automate

Download Trial