3 Replies Latest reply on May 29, 2012 2:32 AM by erinlusby0

    Latest week for a dimension value

      My data is:

       

      Applicant IDWeekLatest Decision StatusFlag
      107559423Unconditional Offer1
      107559429Decline1
      111723922Conditional Offer1
      111723925Decline1

       

      What I would like to have is, if I put a filter on Week <= 23 then each ID/Flag would count the most recent Latest Decision Status; in this case 'Unconditional Offer' and 'Conditional Offer' respectively. But if I were to change the Week <= 26 each ID/Flag would count the most recent Latest Decision, ignoring any previous week's decisions; in this case 'Unconditional Offer' and 'Decline' respectively.

       

      I've attached a packaged workbook of the data.

       

      If you do have a suggested solution, I would be grateful. Also, if possible, providing an example solution in version 6.1 would be appreciated as I've still not updated to version 7.

       

      Thanks in advanced!

        • 1. Re: Latest week for a dimension value
          Tracy Rodgers

          Hi Erin,

           

          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!

           

          -Tracy

          • 2. Re: Latest week for a dimension value

            Hi Tracy,

             

            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

            • 3. Re: Latest week for a dimension value

              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!