Jul
15
Calculating YoY of Quarterly and Yearly Sums in Tableau
Angel works in Finance. She often asks me questions on calculations in a table. Today I got this question: How to calculate Year over Year (YoY) change ratios for both quarterly and yearly sums, in a single sheet?
Here is the solution we got. First there are two parts for the YoY calculation.
For the quarterly YoY, we can apply this formula:SUM([Sales])/LOOKUP(SUM([Sales]), -4) -1
For the yearly YoY, the above formula doesn't work. We need a slightly different one like:
SUM([Sales])/LOOKUP(SUM([Sales]), -1) -1
To combine them in a single one, we got this:
Note that CountD()=1 is equivalent of Max()=Min() when we deal with grand total/subtotal calculations.
Then the results are shown in this table:
The above formula works well logically.
Here is the solution we got. First there are two parts for the YoY calculation.
For the quarterly YoY, we can apply this formula:SUM([Sales])/LOOKUP(SUM([Sales]), -4) -1
For the yearly YoY, the above formula doesn't work. We need a slightly different one like:
SUM([Sales])/LOOKUP(SUM([Sales]), -1) -1
To combine them in a single one, we got this:
Note that CountD()=1 is equivalent of Max()=Min() when we deal with grand total/subtotal calculations.
Then the results are shown in this table:
The above formula works well logically.
