Tolerances and roundings

Tolerance

A tolerance can only be assigned to column defined as a value (so tolerance can't be assigned to key columns) and is supported for column of types numeric and dateTime.

Absolute tolerance

If you apply a tolerance of 0.001 to a numeric column, two rows (with same keys) will be considered as equal if the absolute difference between them is less or equal to 0.001.

This is especially helpful in Business Intelligence when you need to compare two queries and you don’t bother about small differences. Another case in Business Intelligence is also when you customers give you some hints about values expected in reports, they are usually a bit imprecise.

<column  index="2" tolerance="0.001" />

It’s also possible to take some shortcuts and write directly in the equalTo tag the columns’ definition:
<equalTo values="last" tolerance="0.001">
This tag means that all columns are keys except the last which is a value column where a tolerance of 0.001 must applied.

If you want you can combine both notations. The column tag has always a precedence . It means that following notation (for a 6 column’s result set):
<equalTo keys="all-except-last" tolerance="10">
	<column  index="2" role="ignore" />
	<column  index="3" tolerance="0.001" />
</equalTo>

must be interpreted as columns 0, 1 and 4 are keys, column 2 is ignored, column 3 is a value column with a tolerance of 0.001 and finally column 5 is also a value column but with a tolerance of 10.

Tolerance for Date and Time

New in v1.3
If you want to specify the tolerance for dateTime value column, you must express the tolerance in days, hours, minutes, seconds and milliseconds. The correct syntax for two days and an half is

<column  index="3" role="value" type="dateTime" tolerance="2.12:00:00" />

If the tolerance must be set to 15 minutes, you will write:
If you want to specify the tolerance for dateTime value column, you must express the tolerance in days, hours, minutes, seconds and milliseconds. The correct syntax for two days and an half is

<column  index="3" role="value" type="dateTime" tolerance="00:15:00" />

Relative tolerance

New in v1.3
You can express a tolerance, relative to the expected value, by the means of the % symbol. This only applies to numeric values. When comparing the expected and actual value, the comparer will apply a tolerance of the percentage defined.

To illustrate this if you've two rows with values of 40 and 100, an absolute tolerance of 10 will allow the actual values to be respectively in the intervals 30;50 and 90;110. A relative tolerance of 10% will change the intervals to 36;44 and 90;110.

The xml syntax requires a % in the attribute tolerance.
<column  index="3" role="value" type="numeric" tolerance="10%" />

Roundings

New in v1.3
The roundings are another set of tool to express that two values are equal if they are close to each other. At the opposite of tolerance, rounding is applied to both expected and actual values. If after the rounding the operation, the two values are equal the comparison will be positive (and else negative).

The roundings are related to .Net methods Round, Floor and Ceiling. The rounding rules will be the same that their corresponding equivalent in .Net.

The method used must be specified in the column xml definition by the means of values: round, ceiling and floor in the attributeroudning-style.

<column  index="3" role="value" rounding-style="floor" ... />

For numeric columns

Nevertheless, the roundings methods are different in same points to their .Net equivalent. Each of them require a step. If the step is less than 1, the rounding will be applied to the decimal part of the value.

Sample: For a value of 10.52912 with a step of 0.1, the rounding will return
  • with a floor style : 10.5
  • with a round style : 10.5
  • with a ceiling style : 10.6

For a value of 10.52912 with a step of 0.01, the rounding will return
  • with a floor style : 10.52
  • with a round style : 10.53
  • with a ceiling style : 10.53

If the step is greater than 1, the rounding will be applied to the integer part of the value.

Sample: For a value of 10529.12 with a step of 10, the rounding will return
  • with a floor style : 10520
  • with a round style : 10530
  • with a ceiling style : 10530

For a value of 10529.12 with a step of 20, the rounding will return
  • with a floor style : 10520
  • with a round style : 10520
  • with a ceiling style : 10540

<column  index="3" role="value" rounding-style="floor" rounding-step="20" />

For DateTime columns

It's also possible to apply the same kind of roundings for dateTime columns. The rounding must be less than or equal to one day.

Sample: For a value of 2013-10-17 14:47:00 with a step of "1" (day), the rounding will return
  • with a floor style : 2013-10-17 00:00:00
  • with a round style : 2013-10-18 00:00:00
  • with a ceiling style : 2013-10-18 00:00:00

For a value of 2013-10-17 14:47:00 with a step of "00:15:00" (15 minutes), the rounding will return
  • with a floor style : 2013-10-17 14:45:00
  • with a round style : 2013-10-17 14:45:00
  • with a ceiling style : 2013-10-17 15:00:00

Last edited Mar 5, 2014 at 2:57 PM by Seddryck, version 4

Comments

No comments yet.