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 which was at first offering only coffee and tea products, but in 2020 started also to offer ice cream and cake. 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 at 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 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)

Was this helpful?

Yes | Somewhat | No