NOTE: My dataset is pivoted, so is set out as follows: a column for country, a column for years, a column for 'Metric Type' which consists of 'life expectancy', 'fertility rate' and 'population', and I have a column for 'Value' which shows the value of each respective type for each country in a given year. Since this dataset is pivoted, country names appear multiple times in the 'Country' column and years also appears multiple times.
I have created a calculated fields for 'Life Expectancy', 'Population' and 'Fertility Rate'.
To help with the % change calculation, I have created calculated fields for '2000 Life Expectancy' and '2024 Life Expectancy' with the following formula: { FIXED [Country]: AVG(IF INT([Year]) = 1800 AND [Metric] = 'Life Expectancy' THEN [Value] END) }
However this generates all values as NULL. Not sure where I am going wrong or if I'm taking the wrong approach. Some guidance would be greatly appreciated.
If your [Year] is a date field, then the INT value of the date "1800" is not 1800, it's -36,524. Since those two things aren't equal, you'll get NULL results. Dates are just numbers, but they aren't based on the year, they start at 1st January 1900, which has a value of 0, and each day increments by 1, with the hours and minutes being the parts of the day. That's why 1800 is about -(100 x 365)
A value of 1800 would actually be 6th December 1904
You'd need to use YEAR([Year]) = 1800
