This blog is for those who have a basic understanding of table calculation. Very often when we use the table calculation with specific dimensions. It is sometimes quite confusing how the calculation is computed. I learned that selecting and arranging the right specific dimensions on the pane of 'edit table calculation' can make a huge difference, so I would like to share it here.
Let's say we have the following table, which has a hierarchical structure of date (Year>Quarter>Month) with sales and running total of sales side by side. Noted that the running total is the same as sales as I didn't choose any specific dimension yet.
Right-click the second pill of sales and edit the table calculation.
Selecting both quarter and month, but leaving year unchecked means that:
''to calculate the running total of sales for every month and every quarter (which is running down the table), where the calculation re-computes for every year''
Selecting both year and month, but leaving quarter unchecked means that:
''to calculate the running total of sales for every year and every month (which is running down the table), where the calculation re-computes for every quarter''
This becomes harder to understand. A good way to understand it is because the calculation re-computes for every quarter, the calculation will be only finished within each quarter, so only the data within a quarter will be used (e.g. for quarter Q1, only Jan, Feb, and Mar data are used). Also because year is selected, the running total will keep going even finished the calculation from the months in Q1 of the first year. (so 2019 Jan>2019 Feb>2019 Mar>2020 Jan> 2020Feb.....so on)
The last thing is the order. If we select both year and month as before, but month is on top of year, this implies that we calculate the month first, so the running total will be calculated for all the same month first. (2019 Jan> 2020Jan>2021Jan...). So be extra-careful about the order of specific dimensions.
