I'm having a hard time understanding exactly what is wanted. So the Latest Decision Status should only be counted if the week actually equals the input for week and the previous week, otherwise it should return nothing? If you could go into more detail about exactly what you would like to see that would be great!
Thanks for getting back to me about this.
There is historical data for all 52 weeks of the year, with IDs having various decisions given throughout the year. I want to be able to look at the data for any given week and know what the most recent decision has been given for each ID.
So using the above example, if i wanted to know the most recent decisions at week 10 then neither IDs would actually not be counted as they have nothing yet. If I changed and look at week 25, ID 1075594 would have Unconditional Offer' and ID 1117239 would have 'Decline'. If I were to look at week 40 then ID 1075594 &1117239 would have 'Decline', ignoring any previous decisions. Some IDs can have 4 or more decisions made over the process but I always want to look at the most recent decision pertaining to the week I'm looking at. And similarly, ignore and previous or future decisions that have been made for a specific ID.
I also have a field for Decision Date which is what the Week is based on so I'm not sure if it's more useful trying to get the most recent decision to display based on that. Or if in fact, this is at all possible.
I hope this helps clarify. Thanks for your help! Erin
Using another software I think made this work using the formula
=If((Max([Decision Date]) In ([Applicant ID];[Application Number];[Week]))=[Decision Date];[Latest Decision Status])
But I'm not sure how reproduce this formula in Tableau.
Any help would be great! Thanks again!