5 Replies Latest reply on Aug 21, 2018 10:53 AM by Minjeong Hong

    How to count rows all over for "if contains"

    Minjeong Hong

      I'm trying to figure out how to make 'if contains' function count rows all over again every time for each item.

       

      I have a column that has rows with items: Global, National, Sub-national, Municipal/city, and Local/community and they are included multiple times across the rows (it was a multi-select question). I want to create a new dimension so I can get an aggregate of individual items, i.e. total number of Global in the dimension, total number of National in the dimension.

       

      I used this syntax:

      If CONTAINS([Coverage Level Copy], "Sub-national")

      THEN "Sub-national"

      ELSEIF CONTAINS([Coverage Level Copy], "Global")

      THEN "Global"

      ELSEIF CONTAINS([Coverage Level Copy], "Regional")

      THEN "Regional"

      ELSEIF CONTAINS([Coverage Level Copy], "National")

      THEN "National"

      ELSEIF CONTAINS([Coverage Level Copy], "Municipal/city")

      THEN "Municipal/city"

      ELSE "Local/community"

      END

       

      And it only gives me the right number for the first line because for the next lines it only counts the remaining columns, not the entire rows all over again.

      Capture.PNG

       

      I'd appreciate your help! Thanks!

        • 1. Re: How to count rows all over for "if contains"
          Jim Dehner

          The function is working properly

           

          The issues you face are 2 fold -

          first the IF statement will process each record ("row") individually - as soon as it gets a "True" condition it will execute the Then clause and will move on to the next reccord

          so conceptually what are trying to do will not work -

          Suggest you look at splitting the single dimension "Coverage Level Copy"  into individual dimension and re- think how to get the count you need

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: How to count rows all over for "if contains"
            Minjeong Hong

            Hi Jim, thanks for your response.

             

            I also thought about splitting the Coverage Level Copy into multiple other dimensions (as long as there are no multiple items in one cell), but this won't be helpful visualizing it. My end goal is not just counting numbers but essentially visualizing all the items in one view (like Calculation 1 image I attached). Creating multiple syntax for each item won't be helpful either for the same reason.

             

            It'd be ideal if I can make the syntax above examine all records every time a new line starts (if and elseifs until it ends).

            • 4. Re: How to count rows all over for "if contains"
              Ben Neville

              What about if you split it into separate calculations?

              Sub-national
              IF CONTAINS([Coverage Level Copy], 'Sub-national')

              THEN 1

              END

               

              Global

              IF CONTAINS([Coverage Level Copy], 'Global')

              THEN 1

              END

               

              etc.

               

              Now you have parsed the values into individual columns. You can place Measure Names on Rows and Measure Values on Columns, filtering Measure Names to only the values you want to appear in the view. You can use a text table/bar chart (or could even put a more granular field on Detail to create a distribution plot).

              1 of 1 people found this helpful
              • 5. Re: How to count rows all over for "if contains"
                Minjeong Hong

                This works! Thanks Ben!!