3 Replies Latest reply on Oct 3, 2017 5:44 PM by Jennifer VonHagel

    Get Subquery Results in a Bin Count

    Thiago Alpoin

      Hi Folks,

       

      My data consist basically in 4 columns: Store, Client, Gender and Operational System. Below a sample of my data and workbook.

       

      The goal is to identify the stores that contains at least 1 male AND he is using iOs. Then, once the stores identified, count the number of clients in each store - group by bin (1-5 clients; +5clients).

       

      So in the excel spreadhseet below the ideal result would be:

       

      A ✅ (4 clients)

      B ✅ (7 clients)

      C ❌ (12 clients)

      D ✅ (17 clients)

      E ❌ (3 clients)

       

      BIN (# clients)# Stores
      1-51
      +52

       

      My data is coming from a SQL server and my result was accomplished with INNER JOIN subquery to determinate the stores with specific conditions. Then I created a CASE condition to count stores by bin.

       

      Could someone help me out how can I accomplish this task in Tableau without using custom SQL on it?

       

      I sincerely appreaciate any suggestion. Thank You!

       

         

      StoreClientGenderSystem
      AA1maleiOs
      AA2maleiOs
      AA3femaleandroid
      AA4femaleandroid
      BB1maleiOs
      BB2maleandroid
      BB3maleandroid
      BB4femaleandroid
      BB5maleiOs
      BB6femaleiOs
      BB7maleiOs
      CC1femaleandroid
      CC2femaleandroid
      CC3femaleiOs
      CC4maleandroid
      CC5maleandroid
      CC6maleandroid
      CC7maleandroid
      CC8maleandroid
      CC9femaleiOs
      CC10femaleiOs
      CC11femaleiOs
      CC12maleandroid
      DD1maleiOs
      DD2maleiOs
      DD3maleandroid
      DD4maleandroid
      DD5maleandroid
      DD6femaleandroid
      DD7femaleiOs
      DD8femaleiOs
      DD9femaleiOs
      DD10femaleiOs
      DD11femaleiOs
      DD12femaleiOs
      DD13maleandroid
      DD14maleiOs
      DD15maleandroid
      DD16maleiOs
      DD17femaleiOs
      EE1femaleandroid
      EE2femaleiOs
      EE3femaleiOs
        • 1. Re: Get Subquery Results in a Bin Count
          Jennifer VonHagel

          Hi Thiago,

           

          You can create a calculated field to filter your stores. This is a level of detail (LOD) calculation. The FIXED [Store] clause here is kind of similar to grouping by Store in a SQL query.  It will count the number of distinct clients whose gender is male and system is ios - per each store. We then check to see that this count - at the store level - is at least 1.

           

          Store Filter

          { FIXED [Store] :

              COUNTD(IF [Gender] = 'male' AND [System] = 'iOs'

              THEN [Client]

              END)

          } >= 1

           

          See how this plays out in the table. There can only be one possible value - True or False - per each Store, even if each store has multiple records in the table:

          You can see here that if we set the Store Filter to True and count Clients per store - we get all clients per store - even the ones that are not male or do not have ios system:

           

          Ok now we can create bins. This uses the FIXED formula again to count distinct clients per store, as a condition of bin size/group:

           

          Bin:

          IF { FIXED [Store] : [Unique Client Cnt] } >= 1 AND

              { FIXED [Store] : [Unique Client Cnt] } <= 5

          THEN '1-5'

          ELSEIF { FIXED [Store] : [Unique Client Cnt] } > 5

          THEN '+5'

          END

           

           

          Is this what you were looking for?

           

          Workbook is attached, hope it helps,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Get Subquery Results in a Bin Count
            Thiago Alpoin

            Hi Jennifer VonHagel

             

             

            That was AWESOME!

             

            Thank you so much for the detail explanation. All your steps were super clear and easy to reproduce them to my real data.

             

            The results in Tableau now match perfectly with my query results in SQL server.

             

            Thank you again

            • 3. Re: Get Subquery Results in a Bin Count
              Jennifer VonHagel

              Haha, you're welcome, glad it helped!