And I'd definitely be happy to provide any additional explanation or work around any complications you might have!
Thank you so much Joshua. This solves my problem! However I am still a little bit confused why we used "SUM" here? Does the function takes sum by year, or indicator? Many thanks!
1 of 1 people found this helpful
The SUM allows it to be calculated at an aggregate level instead of row by row. The reason that is important is that if it was calculated row by row, then only the numerator or denominator of the calculation would be valid for any row (and possibly neither would be valid).
If you did not use SUM:
(IF [Indicator] = 'A' THEN [Value] ELSE 0 END)
(IF [Indicator] = 'B' THEN [Value] ELSE 0 END)
Then the row
A 1 1990
would end up getting calculated like this:
1 / 0 = NULL
(because the indicator was A and not B for that row)
and the row
B 2 1990
would end up like this:
0 / 2 = 0
(because the indicator was B and not A for that row)
So, you would never get a meaningful answer on any row and you wouldn't be able to aggregate over the years. But, applying the SUM allows it to sum up all the A rows for the numerator and all the B rows for the denominator (split up by Year -- or any other dimension you choose).
Thank you so much Joshua. You've been great help! I now understand the aggregate functions.