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

    Priority rule?


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




      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?

          You can create a CASE statement for status like:



          CASE [Status]

          WHEN 'Discontinued' THEN 1

          WHEN 'Active' THEN 2

          WHEN 'Cancelled' THEN 3



          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?

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


            This worked perfectly! Thank you.