Product operator

When the product of two accounts has to be calculated, it is necessary to distinguish two cases:

  • the dimensions which identifies the value of the two accounts are homogeneous, namely the accounts operate on the same number of dimensions, in this case we talk about homogeneous operator. For example, if we assume we manage on the custom dimension 1 the cost center and on the custom dim 2 the products, we can calculate the value of the products' revenues as the product of the price [A] and the sold quantity [B]. In this cases the two operators are homogeneous since both the value of the account A and the one of the account B are read considering all the elements of the custom dimensions 1 and 2;
  • the dimensions which identifies 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 talk about not homogeneous operators. For example, if we assume we manage on the custom dimension 1 the cost center and on the custom dim 2 the products, we can calculate the value of the products' revenues as the product of a generic price (price of the generic product GEN) [A](Dest2[GEN]) and the sold quantity [B]. In this case the two operators are not homogeneous since the value of the account A is read considering all the elements of the custom dimension 1 while the custom dimension 2 is fixed on GEN and the value of the account B is read considering all the elements of the custom dimension 1 and 2.

In presence of homogeneous operators, the product has a "standard" behavior namely the product is performed between the values whose dimensions correspond exactly to each other.

Example 1

Let us assume we collect data on three custom dimensions: Costs center, Products and Department and we want to calculate the value of the products sale's revenues as the product between price and the sold quantity of each product.

We can write the following TGKML script:

[LO].[50010] := [90020] * [90010]

where:

  • [LO] = target cube (Gross amounts)
  • [50010] = account to calculate( Revenues - Products)
  • [90020] = Input account containing the average price of the product
  • [90010]= Input product containing the sold quantities

INPUT ACCOUNT 90020

Account Custdi1 Custdi2 Custdi3 Amount
90020 D11 D21 D31 20
90020 D11 D22 D32 30

INPUT ACCOUNT 90010

Account custdi1 Custdi2 Custdi3 Amount
90010 D11 D21 D31 4
90010 D11 D21 D32 5
90010 D12 D22 D31 6

OUTPUT ACCOUNT 50010

Account Custdi1 Custdi2 Custdi3 Amount
50010 D11 D21 D31 20 * 4 = 80

Example 2

Let us assume we have to calculate

[LO].[50010] := [90020](Dest2.[GEN]) * [90010]

where:

  • [LO] = target cube (Gross amounts)
  • [50010] = account to calculate ( Revenues - Products)
  • [90020] = input account containing the average price of the product
  • Dest2.[GEN] = element of the custom dimension 2 where the product's average price is stored
  • [90010]= input account containing the sold quantities

INPUT ACCOUNT 90020

Account Custdi1 Custdi2 Amount
90020 D11 GEN 2
90020 D12 GEN 3
90020 D12 D22 4

INPUT ACCOUNT 90010

Account Custdi1 Custdi2 Amount
90010 D11 D21 6
90010 D11 D22 7
90010 D12 D21 8
90010 D12 D22 9
90010 D13 D21 10

OUTPUT ACCOUNT 50010

Account Custdi1 Custdi2 Amount
50010 D11 D21 2 * 6 = 12
50010 D11 D22 2 * 7 = 14
50010 D12 D21 3 * 8 = 24
50010 D12 D22 3 * 9 = 27

Example 3

Let us assume we have to calculate

[LO].[50010](Dest2.Filter([D21],[D22])) := [90020] *[90010]

where:

  • [LO] = target cube (Gross amounts)
  • [50010] = account to calculate ( Revenues - Products)
  • [90020] = input accounts containing the product's average price
  • Dest2.Filter([D21],[D22]) = filter on the elements D21 and D22 of the custom dimension 2
  • [90010]= input account containing the sold quantities

INPUT ACCOUNT 90010

Account custdi1 Custdi2 Custdi3 Amount
90010 D11 D21 D31 20
90010 D11 D22 D32 30
90010 D12 D22 D31 40
90010 D11 D23 D32 50
90010 D12 D23 D31 60

INPUT AMOUNT 90020

Account custdi1 custdi2 custdi3 Amount
90020 D11 D21 D31 4
90020 D11 D22 D32 5
90020 D12 D22 D31 6
90020 D11 D23 D32 5
90020 D12 D23 D31 6

OUTPUT ACCOUNT 50010

Account custdi1 custdi2 custdi3 Amount
50010 D11 D21 D31 20 * 4 = 80
90020 D11 D22 D32 30 * 5 = 150
90020 D12 D22 D31 40 * 6 = 240

Example 4

Let us assume that we must calculate

[LO].[X0000] := [C0000](Dest1.[D12]) *[D0000]

INPUT ACCOUNT C0000

Account custdi1 custdi2 amount
C0000 D11 D21 50
C0000 D12 D22 30

INPUT ACCOUNT D0000

Account custdi1 custdi2 amount
D0000 D11 D21 10
D0000 D11 D22 20
D0000 D12 D22 5

OUTPUT ACCOUNT X0000

Account custdi1 custdi2 amount
X0000 D11 D22 30 * 20 = 600
X0000 D12 D22 30 * 5 = 150

Example 5

Let us assume that we must calculate

[LO].[X0000] := [C0000](Dest1.[D12]) *[D0000](Dest2.[D22])

CONTO INPUT C0000

Account custdi1 custdi2 amount
C0000 D11 D21 50
C0000 D12 D21 40
C0000 D12 D22 30

INPUT ACCOUNT D0000

Account custdi1 custdi2 amount
D0000 D11 D21 10
D0000 D11 D22 20
D0000 D12 D22 5

OUTPUT ACCOUNT X0000

Account custdi1 custdi2 amount
X0000 D11 D21 40 * 20 = 800
X0000 D12 D21 40 * 5 = 200
X0000 D11 D22 30 * 20 = 600
X0000 D12 D22 30 * 5 = 150

Example 5

Let us assume that we must calculate

[LO].[X0000] := [C0000](Dest1.[D11]) *[D0000](Dest1.[D12])

INPUT ACCOUNT C0000

Account custdi1 custdi2 amount
C0000 D11 D21 50
C0000 D12 D21 40
C0000 D12 D22 30

INPUT ACCOUNT D0000

Account custdi1 custdi2 amount
D0000 D11 D21 10
D0000 D11 D22 20
D0000 D12 D22 5

OUTPUT ACCOUNT X0000: This account is empty because the products doesn't give any result.