Did you ever get this working?
No, I still haven't figured this out. I think it has something to do with my "Selected over Text" and how it is being computed as a table calculation, but I can't figure out how to fix it.
See the attached, I'll leave validating the numbers to you, at a cursory glance it seems correct. I've attached a revised workbook, and here's what I found:
The calculations were coming up with incorrect results because the Compute Using was not set correctly. All three calcs (Selected Over Text, Compared to weight, and Difference in weight) use the same Compute Using settings, which is a nested Compute Using where Year Ago Weight is set to Compute Using Year, and Previous Weight has an Advanced Compute using of Year/Date Level ordered along Date/Minimum/Ascending. I've included screenshots for each:
This should get you the desired results. With table calculations, the ordering of the fields on the right side of the Advanced dialog and all the other settings are critical to getting the desired output, and it can be easy to forget to update something when you are dragging and dropping pills in the view.
The Null results are coming because the Selected Over Text field depends on a LOOKUP of the prior value, and when you are at the earliest value(s) there are no prior values and the LOOKUP is either returning Null or something that is ultimately interpreted as Null in the calculations. The original view that you worked from does not show any Null results, and I can't figure out exactly why, I suspect this might have something to do with the fact that the original is using pasted text and you are using an Extract. In any case, you can get rid of the Null values by dragging the Selected Over Text to the Filters shell and de-selecting Null. (It's good practice to do this *after* you've gotten the Compute Using settings right, so that way you only have to set them once).
Also you can now create the MIN(Date) field, drag that to the left side of the Rows window, and uncheck Show Header to create a sort on date.
Finally, the Selected Over Text when the Select Date Level parameter is set to Week is not returning the expected text results. This is due to how Tableau's STR() and DATE() functions work differently on extracts vs. text files, see this thread from the last couple of days for details on what you can do:
Let me know if this helps!
Thank you so much! This is exactly what I was missing! Thank you for the screenshots, that helped.
I still haven't figured out the week text, but that is something I can continue to work on.
I appreciate you looking at this and helping me!