9 Replies Latest reply on Oct 21, 2016 12:31 PM by David Li

    Need a formula to consolidate multiple dimensions

    Ian Johnson

      I'm working with 5 dimensions each representing 5 subscription types.  The users can be a member of 1 ,2, 3, 4, or 5 of the Subscriptions.

      1. IT-Subscription

      2. RC-Subscription

      3. CQ-Subscription

      4. SC-Subscription

      5. PN-Subscription

       

      Here's an example of what I'm trying to do.

       

      When they are only a member of a single subscription i'd like to show that subscription (Example if the member was only assigned to IT-Subscription it would say "Information Technology") .BUT they can be a member of any combination of subscriptions SO  When they are a member of 2 or more subscriptions I'd like to display "Multiple Subscriptions" .

       

       

      Here is one formula I came up with but it's not working because it's following the order of the formula.

       

      if NOT ISNULL([IT-Subscription])THEN "Information Technology"

      ELSEIF  not ISNULL([RC-Subscription])THEN "Revenue Cycle"

      ELSEIF NOT ISNULL([CQ-Subscription])THEN "Cost and Quality"

      ELSEIF NOT ISNULL([SC-Subscription])THEN "Supply Chain"

      ELSEIF NOT ISNULL([PN-Subscription])THEN "Physician Network"

      ELSE "Multiple Subscription"

      END

       

      I also tried a case but it's not what I need.

       

      if NOT ISNULL([IT-Subscription])THEN "Information Technology"

      ELSEIF not ISNULL([RC-Subscription])THEN "Revenue Cycle"

      ELSEIF NOT ISNULL([CQ-Subscription])THEN "Cost and Quality"

      ELSEIF NOT ISNULL([SC-Subscription])THEN "Supply Chain"

      ELSEIF NOT ISNULL([PN-Subscription])THEN "Physician Network"

       

       

      END