10 Replies Latest reply on Jun 9, 2016 2:52 PM by Gurpreet Singh Goraya

    Count items

    Gurpreet Singh Goraya

      Hello Team,

       

      I am trying to count and create the four groups of the following table:

       

      2016-06-09_9-15-53.jpg

      1. Client : The client group will include CLI1+(CLI1,CU61)+(CLI1,CU6,ESC1)+(CLI1,CU62)+(CLI1,ESC1)+(CLI1,ESC1,MA1)+(CLI1,ESC2)+(CLI1,MA1)+(CL2)+(CLI2,ESC1)+CLI1

      2. Child:  The child group will include (CLI1,CU61)+(CLI1,CU6,ESC1)+(CLI1,CU62)+CU61

      3. ESC: The ESC group will include  (CLI1,CU6,ESC1)+(CLI1,ESC1)+(CLI1,ESC1,MA1)+(CLI1,ESC2)+(CLI1,ESC2)+(CLI2,ESC1)+ESC1+ESC2

      4. MA: The MA group will include (CLI1,ESC1,MA1)+(CLI1,MA1)+(ESC1,MA1)+MA1

       

      Thank you in advance.

       

      Gurpreet

        • 1. Re: Count items
          khalid norat

          There are 2 ways of going around this.

           

          1 - use Calculated Fields and If Statements

          2. Use 'create Group '

           

          If you can share a sample workbook I can demonstrate on your workbook.

          If you are unable to do so an excel file with some sample data to work with would also help

          • 2. Re: Count items
            khalid norat

            Also can I simplify the requirement for you

             

            Client : Contains 'CLI'

            Child : Contains 'CU'

            Esc: Contains 'ESC'

            MA: Contains 'MA'

             

            Hopefully this matches your requirement.

             

            Writing calculated fields for this would be easier and amending for future changes would be a lot less work.

             

             

             

            Please do share a sample and I can help you with this

            • 3. Re: Count items
              Gurpreet Singh Goraya

              Thank you Khalid,

               

              I tried the following yesterday but the results were not as expected. I used Calculated field with the IF statement as following:

               

              IF [Passengertype] =  "CLI1" OR [Passengertype] = "(CLI1,CU61)" OR [Passengertype]= "(CLI1,CU61,ESC1)"OR [Passengertype] ="(CLI1,CU62)" OR [Passengertype]="(CLI1,ESC1)" OR [Passengertype]="(CLI1,ESC1,MA1)"OR [Passengertype]="(CLI1,ESC2)" OR [Passengertype]="(CLI1,MA1)" OR [Passengertype]="(CLI2,ESC1)" OR [Passengertype]="(CLI2)" OR [Passengertype]="(CLI3)"  then "Client"

              ELSEIF

              [Passengertype]="MA1" OR [Passengertype]="(CLI1,ESC1,MA1)" OR [Passengertype]="(CLI1,MA1)" OR [Passengertype]="(ESC1,MA1)" then "Mandatory"

              Elseif

              [Passengertype]= "ESC1" OR [Passengertype]="(CLI1,CU61,ESC1)" OR [Passengertype] ="(CLI1,ESC1)" OR [Passengertype]="(CLI1,ESC2)" OR [Passengertype]="(CLI2,ESC1)" OR [Passengertype]="(ESC1,MA1)" then "Escort"

              Elseif

              [Passengertype]="CU61" or [Passengertype] ="(CLI1,CU61)" OR [Passengertype] ="(CLI1,CU61,ESC1)" OR [Passengertype]="(CLI1,CU62)" then "Child"

              ELSE " NOT DEFINED "

              END

               

              Sorry, due data sensitivity I cannot share sheet with you. Thanks

              GG

              • 4. Re: Count items
                Gurpreet Singh Goraya

                I also have tried contain operator too. But no luck. Thanks

                • 5. Re: Count items
                  Gurpreet Singh Goraya

                  Any luck Khalid?

                  • 6. Re: Count items
                    Joshua Milligan

                    Gurpreet,

                     

                    It looks like the difficulty you are having is that some groups have overlap.  So there could be values that need to be counted for one group and then another.  That makes creating a calculated field for the group itself almost impossible, because per record you can only calculated one group value.

                     

                    So, an alternate solution is to create a measure for each group and then use Measure Names / Measure Values to group the counts.

                     

                    To do this, create a calculated measure for each group.  For example:

                     

                    [Client]

                    IF CONTAINS([Passengertype], "CL") THEN 1 ELSE 0 END

                     

                    [Child]

                    IF CONTAINS([Passengertype], "CU") THEN 1 ELSE 0 END

                     

                    Then you can use the Measure Names field to give you the group name and Measure Values to visualize the values in the view.

                     

                    Hope that helps!

                     

                    Best Regards,

                    1 of 1 people found this helpful
                    • 7. Re: Count items
                      Gurpreet Singh Goraya

                      Thank you Khalid, I have updated the workbook and wants to share with you so you can demonstrate on it. I am not finding any link to attach worksheet. Thanks

                       

                      Gurpreet

                      • 8. Re: Count items
                        Gurpreet Singh Goraya

                        Thank you very much Joshua. It works for me.

                        • 9. Re: Count items
                          Joshua Milligan

                          Glad to hear, Gurpreet! 

                          If you think it is an answer that will help others, please mark it as correct.  Thanks!

                           

                          -Joshua

                          • 10. Re: Count items
                            Gurpreet Singh Goraya

                            Hello Joshua, it was a great help. I was able to create my group accordingly. The only challenge I am facing now, I cannot bring all groups together on the canvas. I can only see one group at a time. I am pretty sure, there could be a way to bring all groups together but I am pretty new with Tableau. Thanks again.

                             

                            Gurpreet