Calculations with Non-Overlapping Dimensions
This page should help you to understand how the calculation of Nodes with different Dimensions works. Here you can learn about Dimensions and data upload. Just a reminder, when it comes to Dimensions, Valsight uses 3 specific terms:
Dimension | ProductDim |
Dimension Levels | e.g. Product Category, Product, Product variant |
(Level) Values | for Product Category e.g. Sweets; Baked Goods; Soft Drinks |
Setting
The setting describes a street stand that was at first offering only coffee and tea products, but in 2020 started also to offer ice cream and cake. The following data are used for demonstration purposes.
Input 'Sales'
Year | Product Category | Product | Sales |
2019 | Hot drinks | Coffee | 100 |
Tea | 200 | ||
2020 | Hot drinks | Coffee | 100 |
Tea | 200 | ||
Sweets | Cake | 300 | |
Ice cream | 400 | ||
Totals | 1,300 | ||
Input 'Variable Cost Rate'
Product Category | Variable Cost Rate |
|---|---|
Hot drinks | 20% |
Sweets | 50% |
Input 'FixedCost2019'
Year | Fixed Cost |
|---|---|
2019 | -100 |
Input 'FixedCost2020'
Year | Fixed Cost |
|---|---|
2020 | -200 |
We want to look into the ADDITION (+) & SUBTRACTION (-) as well as MULTIPLICATION (*) & DIVISION (/), because other formulas are mostly based on these. Please note that SUBTRACTION (-) behaves the same as ADDITION (+) and DIVISION (/) behaves as MULTIPLICATION (*). When working with the Dimensions, there are 3 cases: fully matching Dimensions and level values, non-matching level values, and non-matching Dimensions or their levels.
Matching Dimensions & Level Values
This is the simplest case. The elementary arithmetic operations just match all the dimensions and their levels.
'Sales' + 'Sales' =
Year | Product Category | Product | Sales |
|---|---|---|---|
2019 | Hot drinks | Coffee | 200 |
Tea | 400 | ||
2020 | Hot drinks | Coffee | 200 |
Tea | 400 | ||
Sweets | Cake | 600 | |
Ice cream | 800 | ||
Totals | 2,600 | ||
'Sales' * 'Sales' =
Year | Product Category | Product | Sales |
|---|---|---|---|
2019 | Hot drinks | Coffee | 10,000 |
Tea | 40,000 | ||
2020 | Hot drinks | Coffee | 10,000 |
Tea | 40,000 | ||
Sweets | Cake | 90,000 | |
Ice cream | 160,000 | ||
Totals | 350,000 | ||
Non-Matching Level Values
With non-matching level values, ADDITION (+) & SUBTRACTION (-) just stack the present level values. Items multiplied or divided with Node without matching level value result in null and show an error.
'FixedCost2019' + 'FixedCost2020'
Year | Fixed Cost |
|---|---|
2019 | -100 |
2020 | -200 |
'FixedCost2019' * 'FixedCost2020'
Year | Fixed Cost |
|---|---|
2019 | 0 |
2020 | 0 |
Non-Matching Dimensions or Dimension Levels
With non-matching Dimensions or Levels, ADDITION (+) & SUBTRACTION (-) will first aggregate according to the Aggregation setting to the common set of Dimensions and Dimension depths and then perform the action. MULTIPLICATION (*) & DIVISION (/) is performed at the common set of Dimensions and Dimension depths. In contrast to Addition & Subtraction, the result gets assigned the joined set of Dimensions and their levels.
'Variable Cost' = -1 * 'Sales' * 'Variable Cost Rate' =
Year | Product Category | Product | Sales |
|---|---|---|---|
2019 | Hot drinks | Coffee | -0.2 * 100 = -20 |
Hot drinks | Tea | -0.2 * 200 = -40 | |
2020 | Hot drinks | Coffee | -0.2 * 100 = -20 |
Hot drinks | Tea | -0.2 * 200 = -40 | |
Sweets | Cake | -0.5 * 300 = -150 | |
Sweets | Ice cream | -0.5 * 400 = -200 | |
Totals | -470 | ||
'Gross Margin' = 'Sales' + 'Fixed Cost2019' + 'FixedCost2020' + 'Variable Costs' =
Year | Gross Margin |
|---|---|
2019 | 300 + (-20 + -40) - 100 = 140 |
2020 | 1000 + (-20 -40 - 150 - 200 ) - 200 = 390 |
Totals | 530 |
(Sum is set as the aggregation type)