2 Replies Latest reply on Mar 8, 2016 12:48 PM by dipesh.patel.4

    Priority rule?

    dipesh.patel.4

      Is there a way to create a calculated field to select a value based on priority rules?

       

      Example:

       

      I have a customer ID, date, and status. There are some cases where a customer has multiple statuses on the same date. I have some business rules to say which status should be selected for those cases.

       

      See attached workbook.

       

      Here are the rules:

      • Discontinued gets first priority
      • if there is no discontinued, then active gets priority
      • if there is no discontinued or active then cancelled gets priority
        • 1. Re: Priority rule?
          pooja.gandhi

          You can create a CASE statement for status like:

           

          [Priotity]

          CASE [Status]

          WHEN 'Discontinued' THEN 1

          WHEN 'Active' THEN 2

          WHEN 'Cancelled' THEN 3

          END

           

          And another field over that CASE statement like:

           

          {fixed [ID], [Status Date]: max( if [Priority] = { fixed [ID], [Status Date] : MIN([Priority]) } then [Status] end ) }

           

          What about 'pending' status? you did not mention that, so I left it as is. Hopefully this gives you ideas though!

           

          1 of 1 people found this helpful
          • 2. Re: Priority rule?
            dipesh.patel.4

            Pending is always last, that's why I didn't include it.

             

            This worked perfectly! Thank you.