QCD Add-on

14. Consolidation

14.1. Principles

The consolidation feature allows you to combine the values of one or more indicators (called “sources”) into another indicator (called “recipient indicator”). Source indicators can belong to any ring, of any letter, of any board, of any room, of your domain.

Note

The values used for the binary valorization mode are always 0 and 1.

When consolidating binary indicators, only the button values (0 and 1) are used.

14.2. Settings

Whenever you create a new indicator, you can make it a recipient of values from other indicators through the consolidation feature. You just have to click on the icon shown below.

../_images/qcd-guide-82-01.png

Note

  • A binary indicator cannot be a recipient indicator
../_images/qcd-guide-82-02.png
  • Only room administrators can set up a consolidation

Below you will find the interface for the consolidation set-up:

../_images/qcd-guide-82-03.png

1 -> Ring selection

2 -> Type of formula selection

3 - > Select whether or not to take into account values not entered in boxes

4 -> Set-up display and edition of this indicator in the box menu

5 -> Decimal digits after consolidation

6 -> Selection and editon of formula input

7 -> Formula element deletion

8 -> Formula

Note

When all source values are empty and the “Empty values” option is disabled (empty values not ignored), the target (consolidated values) is empty

14.2.1. Available formulas

Five consolidation formulas are available: sum, average, minimum, maximum and custom.

Sum

The recipient indicator will receive the sum of the values of source indicators
../_images/qcd-guide-82-05.png

In this example the value of the recipient = value of indicator 1 + value of indicator 2 + value of indicator 3

Average

The recipient indicator will receive the average of the values of source indicators

Note: If one or more source indicators have a missing value, they will be ignore in the calculation. For example: If we consolidate 8 indicators and one of them has a missing value, we only consider 7 values.
../_images/qcd-guide-82-06.png

In this example the value of the recipient indicator = (value of indicator 1 + value of indicator 2 + value of indicator 3) / 3

Minimum

In this case, the target indicator retrieves the minimum values of each source indicator.

  

Maximum

In this case, the target indicator retrieves the maximum values of each source indicator.

  

Custom

With the custom formula you are free to define any formula by combining arithmetic operators with source indicators and fixed numbers with a simple click or a drag&drop
../_images/qcd-guide-82-07.png
../_images/qcd-guide-82-08.png

In this example the value of the recipient indicator = (value of indicator 1 + value of indicator 2)/100

Note

To calculate the MIN and MAX of several indicators, use a comma to separate the indicators.

The syntax of the formula must be correct. If not, a red error message will be displayed and the formula will not be taken into account.

../_images/qcd-guide-82-09.png

14.2.2. Formula elements by type of formula

. . Type of formula selection
Element type Possibilities Sum Average Minimum Maximum Custom
Recipient Indicator

Every source indicator:

  • is defined with the following path: room → board → letter → ring
  • may follow a time aggregation rule
 ✔️ ✔️ ✔️ ✔️ ✔️
Arithmetic operator

6 basic operators:

  • Brackets
  • Arithmetic: sum +, subtraction - , multiplication *, division /
 ❌ ❌ ❌ ❌ ✔️
Fixed number Fixed numerical value entered manually ❌ ❌ ❌ ❌ ✔️

14.2.2.1. Source indicator management

14.2.2.1.1. Target and recipient ring frequency compatibility

The frequency of the ring the recipient indicator belongs to must be lower than the frequencies of the rings target indicators belong to (ie.: you cannot consolidate a quarter into a month).

14.2.2.1.2. Time aggregation rules

Aggregation rules must be defined when the frequency of source rings is higher than the one of recipient rings (ex.: 31 boxes of a month to be consolidated in one month). In this case you have the choice between:

  • Aggregating values with a sum
  • Aggregating values with an average
../_images/qcd-guide-82-11.png
14.2.2.1.3. Lifecycle

Some elements may be changed over time, after the consolidation is setup and running. In this case, when you open the consolidation setup window, some indicators may be displayed:

  • With an orange border, if a source indicator was renamed
  • With a red border, if the source indicator was erased or the user has no access to it

This may occur in case of data import-export.

14.3. Consolidation trigger

A consolidation can be triggered in many ways:

Automatically by clicking on a board If the switch is on in the utility panel
From the QCD utility panel
../_images/qcd-guide-82-12.png

1 - Consolidation of today’s or yesterday’s source values only

2 - Consolidation time window

3 - Consolidation trigger

4 - Automatic trigger management
Directly from a letter
../_images/qcd-guide-82-13.png

Note

  • You cannot trigger a consolidation on a time window wider than the current time window of a letter (ex. : for a letter displaying the values of march 2019, the consolidation can triggered between march 1st and march 31st)
  • Users with simple editing rights can trigger a consolidation

14.4. Result display

14.4.1. On the letter

A status report will pop up on the letter :

  • In black if the consolidation succeeded
  • In red if an error occurred
../_images/qcd-guide-82-14.png

1 -> Trigger date

2 -> Activator user name

3 -> Formula reminder for custom formulas

4 -> Number of boxes with a successful consolidation

5 -> Number of boxes with a consolidation error

6 -> Number of boxes to be still calculated

14.4.2. On indicators

In the letter menu an icon will show recipient indicators.

In case of errors in a consolidation this icon will turn red.

By clicking on it you will see the details of the consolidation, that will be described with the following signs:

  • ‘#’ if the box of the source indicator wasn’t found or ‘~’ in case of consolidation by average
  • ‘@’ if the box of the source indicator was disabled
../_images/qcd-guide-82-15.png

This screen allows you to see all the details of your indicator formula.

Tip

Click on the indicator’s box to be directed to the source letter.

14.5. Results modification

If the value of a consolidation is changed manually, the consolidation status will be updated accordingly: the icon and the detail will be blue.

From that moment on, this specific value will not be affected by future consolidations.

In order to recalculate the initial consolidated value, the manually changed value is to be deleted and a new consolidation launched.

14.6. Limitations

14.6.1. Consolidation settings lifecycle

When a room administrator sets up a consolidation in a given room, he needs the rights of access to the room where source indicators are located.

Thus he can set up a “link” between the “recipient” room and “source” rooms.

Once this “link” is setup, it will stay even if the administrator of the recipient room loses his right to access the source room. Only this specific recipient room administrator will have the right to delete this “link”.

14.6.2. Data import-export

Exported data include consolidation settings. This means that if you export a QCD board with a consolidated indicator and then you re-import it, the consolidated indicator will keep its initial settings.