Sum operator

When two accounts have to be summed up, two cases have to be distinguished:

  • the dimensions that identify the value of the two accounts are homogeneous, namely the accounts operate on the same number of dimensions; in this case we have homogeneous operands. For example, if we assume to mange the custom dimensions 1 and 2 we can have [A] + [B]. In this case, the two operands are homogeneous since both the value of the A account and the value of the B one are read taking into consideration all the elements of the custom dimensions 1 and 2;
  • the dimensions that identify the value of the two accounts are not homogeneous, namely the accounts do not operate on the same number of dimensions; in this case we have non-homogeneous operands. For example, if we assume to manage the custom dimensions 1 and 2 we can have [A] + [B](Dest2.[GEN]). In this case, the two operands are not homogeneous since the value of the A account is read taking into consideration all the elements of the custom dimensions 1 and 2 while the value of the B account is read taking into consideration all the elements off the custom dimension 1 while the custom dimension 2 is fixed at GEN.

In presence of homogeneous operands the sum has a "standard" behavior and the TGKML script does not require a specific syntax, unlike the non-homogeneous operands that require the BOUND syntax.

Example 1

If we assume to perform the data entry on three custom dimensions: Cost centers, Products and Departments and that we want to calculate the value of the total revenues as sum of the revenues given by the products sale and of the other revenues

We can write the following TGKML script:

[LO].[50099] := [50010] + [50020]

where:

  • [LO] = target cube (Gross amounts)
  • [50099] = account to calculate( Total revenues)
  • [50010] =input account containing the products revenue
  • [50020]= input account containing the other revenues

INPUT ACCOUNT 50010

Account CustDim1 CustDim2 CustDim3 Amount
50010 D11 D21 D31 5
50010 D11 D22 D32 6

INPUT ACCOUNT 50020

Account CustDim1 CustDim2 CustDim3 Amount
50020 D11 D21 D31 7
50020 D11 D22 D32 8
50020 D12 D21 D31 9

OUTPUT ACCOUNT 50099

Account CustDim1 CustDim2 CustDim3 Amount
50020 D11 D21 D31 5 + 7 = 12
50020 D11 D22 D32 6 + 8 = 14
50020 D12 D21 D31 0 + 9 = 9

Thus, the system sums up the records of the two accounts which match perfectly on all the dimensions and just rewrites those that do not match.

Let's see some examples to better understand how the sum operator works.

Let us assume to manage the custom dimensions 1 and 2 and that we want to sum up the values of the C00000 e D00000 revenue accounts. Moreover, let us assume to have following data on the database.

CONTO INPUT C0000

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 C00000 2013BDG $AMOUNT D11 D21 01 50
A00 C00000 2013BDG $AMOUNT D11 D22 01 30

CONTO INPUT D00000

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 D00000 2013BDG $AMOUNT D11 D21 01 10
A00 D00000 2013BDG $AMOUNT D11 D22 01 20
A00 D00000 2013BDG $AMOUNT D12 D23 01 5

Example 2. Sum with NON homogeneous operands.

[LO].[X00000] := [C00000](Dest1.[D11]) + [D00000];

In this case the value of the single operands is:

[C00000](Dest1.[D11])

Entity Account Scenario Category Dest2 Period Amount
A00 C00000 2013BDG $AMOUNT D21 01 50
A00 C00000 2013BDG $AMOUNT D22 01 30

[D00000]

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 D00000 2013BDG $AMOUNT D11 D21 01 10
A00 D00000 2013BDG $AMOUNT D11 D22 01 20
A00 D00000 2013BDG $AMOUNT D12 D23 01 5

The system sums up the records that match on all the involved dimensions except the one where the reading from fixed point has been performed.

Thus the result is:

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 X00000 2013BDG $AMOUNT D11 D21 01 50 + 10
A00 X00000 2013BDG $AMOUNT D11 D22 01 30 + 20

the third row of the D00000 account disappears because the D23 element of the custom dimension 2 does not have any equivalence in the C00000 account.

Thus, generally data might be lost when non homogeneous operands are summed up

Example 3. Sum with NON homogeneous operands changing order.

The previous example concerns the sum between two non homogeneous operands. If the operands are more than two, the order in which the operations are performed is important.

Let us assume to manage the two custom dimensions 1 and 2 and that we want to sum up the values of the C00000, D00000 and F00000 revenue accounts. Moreover, assume that we have following data on the database.

CONTO INPUT C0000

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 C00000 2013BDG $AMOUNT D11 D21 01 10
A00 C00000 2013BDG $AMOUNT D11 D22 01 20

CONTO INPUT D00000

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 D00000 2013BDG $AMOUNT D11 GEN 01 30
A00 D00000 2013BDG $AMOUNT D12 GEN 01 40

CONTO INPUT F00000

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 F00000 2013BDG $AMOUNT D11 D21 01 50
A00 F00000 2013BDG $AMOUNT D11 D22 01 60
A00 F00000 2013BDG $AMOUNT D12 D21 01 70
A00 F00000 2013BDG $AMOUNT D12 D22 01 80

The result of the script

[LO].[X00000] := [C00000] + [D00000](Dest2.[GEN]) +[F00000]

is

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 X00000 2013BDG $AMOUNT D11 D21 01 10+30+50
A00 X00000 2013BDG $AMOUNT D11 D22 01 20+30+60
A00 X00000 2013BDG $AMOUNT D12 D21 01 70
A00 X00000 2013BDG $AMOUNT D12 D22 01 80

While, if we invert the operands' order, namely we consider the script

[LO].[X00000] := [F00000] + [D00000](Dest2.[GEN]) + [C00000]

the result is:

Entity Account Scenario Category Dest1 Dest2 Period Amount
A00 X00000 2013BDG $AMOUNT D11 D21 01 10+30+50
A00 X00000 2013BDG $AMOUNT D11 D22 01 20+30+60
A00 X00000 2013BDG $AMOUNT D12 D21 01 70+40
A00 X00000 2013BDG $AMOUNT D12 D22 01 80+40

and thus the result of a sum with non homogeneous operands changes reverting the operands' order.