2 of 2 people found this helpful
With your data structured as it is above, I would probably do the following (it'll be a bit tedious, especially the more years you have, but it works!):
- First, make sure all the "x" values turn into NULLs. The easiest way, based on the sample data above, is to tell Tableau Prep that the fields are numeric (whole numbers). Anything that's not a number will turn into NULL.
- Then, for each year (thus the tediousness) create a calculation to replace the year:
2018 will be the first non-NULL value working backwards:
2017 gets a bit less complex:
2016 is even better:
2015, being the first year, doesn't even require a calculation
Now, you may well have more years in your data and that's going to be more fun! So if you went back to 2011, then 2018 will look like:
If you had, say, a hundred years of data, I'd start to consider another approach in terms of pivoting the data and then doing some crazy joins and other logic, It avoids the tediousness of writing a bunch of calcs, but as you can see going down that path does not reduce the complexity:
Hope that helps!
This is GREAT!!!! I only am going back 5 years, so the calc you provided will work. Thank you so much for your help. I really appreciate it!!
You're welcome! Happy to help!