Hello!
How would I create a calculated field that calculates a ratio between the sum of votes for a given state between a designated pair of years? For example: I'd like to calculate the ratio of votes cast in "TEXAS" in the year 2020 to 2016. I tried calculating the specific sum, but couldn't get it to work out exactly. I could also only think of a way to do this on a state-by-state basis, and would love to learn how to do it on a general scale so I can apply it to all 50 states.
Here is my conceptual code:
(IF attr([Year]) = 2020 AND attr([State]) = 'TEXAS' THEN sum([Candidatevotes]) END)
/
(IF attr([Year]) = 2016 AND attr([State]) = 'TEXAS' THEN sum([Candidatevotes]) END)
Ideally, this would return something like "1.5" in the invent there were 1.5 times as many votes in 2020 than 2016. I've also attached my workbook so you can get a better idea of what I'm trying to accomplish.
Actually, everything depends on whether and how you use the Year dimension on your viz. If I refer to your 'Test' worksheet, Year is absent from it, so basically your viz is at the aggregate level of all year. Hence, if you test ATTR(Year), you just get *, that's why your formula does not work. You have to do it the other way round, i.e. test year at detailed level and then aggregate the results (and of course, you don't include a condition on State):
SUM(IF [Year] = 2020 THEN [Candidatevotes] END)
/
SUM(IF [Year] = 2016 THEN [Candidatevotes] END)
---
Antoine Dinimant
Freelance consultant, trainer & teacher
