I have a data at Country Year Level (2018 to 2022). I want to create a new field wherein, for Each Country I would like to take a difference of, lets say Perc Value Sales which is an existing field for each year with First Year. Here my first year will start from 2019 instead of 2018.
Note:- I want to create new field and not a visualization.
Will repeat the same exercise for another field and then need to take a correlation.
Attaching the dummy data and below is snapshot of expected values.
Attaching the Dashboard as well.
Hi @Bhavin Nagda,
LoD functions calculate an aggregate at the level you specify, so in this case it's each [Country]. The calculation after that must be an aggregate (i.e. SUM, AVG, MAX etc.). Tableau takes the result of the calculation and applies it to all of the rows that apply to that country. In this case, it wouldn't matter if I used MAX, MIN, or AVG, because there is only one row where the calculation returns a value:
IF [Year] = [Country Earliest Year] THEN [Perc Value Sales] END
// Only returns a value when the year on this row is the same as the earliest year for the country
So, say there are 5 years (2015-2019), each with sales of £100, it runs through the calculation above for each of them and gets the values 500, NULL, NULL, NULL, NULL. The NULLs because 2016 != 2015, 2017 != 2015 etc. Subsequently, it takes the MAX of that list to apply to that country. NULL values are not considered. So, it doesn't matter if I did MAX, MIN, AVG, SUM, the answer would still be 500, but I have to choose 1 for the syntax of the FIXED LoD.
