7 Replies Latest reply on Sep 10, 2018 7:52 AM by Cathi Champion

    Count record if two dimensions are equal

    Cathi Champion

      I am very new at Tableau and am trying to replicate this query:

      SELECT COUNT(DISTINCT(FIRST_account_id)) AS FIRST_id, COUNT(DISTINCT(SECOND_id)) AS SECOND_count, SUM(num_calls) as call_count

      from DATA SOURCE

      where record_date BETWEEN '08-26-2017' and '08-25-2018'

      and num_calls > 0

      and status = 200

      AND SECOND_id = THIRD_id

       

      The data source has three IDs. I want a result that counts only records where the SECOND and THIRD ids are equal. After many searches and reading many examples, I just haven't found the right approach. It seems like it should be simple, but looking at parameters, filters and calculated fields examples, I've not found the answer. Can anyone point me to an approach example, or explain how?

       

      Thanks.

        • 1. Re: Count record if two dimensions are equal
          Deepak Rai

          Can you attach fake data set and expected result from it?

          • 2. Re: Count record if two dimensions are equal
            Dipankar Roy

            Hi Cathi,

             

            From what i understand,

            1. You have 3 ids, First_account_id, SECOND_id and THIRD_id

            2. You want to count the distinct SECOND_id group by distinct First_account_id

            3. You also want the sum of num_calls across the First_account_id

            4. Condition - num_calls > 0 and SECOND_id = THIRD_id

             

            Step 1: Pull First_account_id in the rows, change measure from the drop down to Count(distinct)

            Step 2: Pull SECOND_is next to First_account_id and again change measure to Count(distinct)

            Step 3: Drag num_calls to Text marks card and change measure to Sum

            Step 4: Pull num_calls to Filter shelf and set > 0

            Step 5: Create a calculated field with the definition: SECOND_id = THIRD_id. Pull it to the Filters shelf and select True option

             

            You should have your solution. In case you don't, reply to this thread. BTW, sharing the workbook will be a great help.

            Please mark this as correct answer if it solves your issue.

             

            Thanks,

            Dipankar

            • 3. Re: Count record if two dimensions are equal
              Cathi Champion

              Giving it a try. All these records qualify by date, some qualify by status, any FIRST id is fine, and summing calls is just that, but the requesters want to know how many times in the data set that the SECOND id = THIRD id. In this, the result would be 4 and we would know that because we were able to identify when SECOND = THIRD

                     

              FIRST_account_idSECOND_idTHIRD_idnum_callsrecord_datestatus
              A12B34C5613572468765432101/1/2018200
              A12B34C57135724713572473710/2/2017200data in results for SECOND_id = THIRD_id
              A12B34C58135724887654344001/3/2018200
              A12B34C591357249876543590,0001/4/2018200
              A12B34C601357250135725087548/7/2108200data in results for SECOND_id = THIRD_id
              A12B34C61135725187654373901/6/2018500
              A12B34C6213572528765438561/7/2018200
              A12B34C6313572538765439201/8/2018200
              A12B34C6413572541357254704/3/2018200data in results for SECOND_id = THIRD_id
              A12B34C651357255876544154,0001/10/2018500
              A12B34C66135725687654425891/11/2018200
              A12B34C67135725787654431,7421/12/2018200
              A12B34C68135725813572581,95412/15/2017200data in results for SECOND_id = THIRD_id
              A12B34C69135725987654458731/14/2018200
              • 4. Re: Count record if two dimensions are equal
                Deepak Rai

                Thanks

                Deepak

                If It Helps, Pl Mark it Helpful and CORRECT to Close Thread

                • 5. Re: Count record if two dimensions are equal
                  Cathi Champion

                  None of these got the right answer compared to the SQL query. Since I cannot share the real data, though, I'm going to call this closed. There are some clues I can include in my search for the right result. Thanks much for those clues.

                  • 6. Re: Count record if two dimensions are equal
                    Deepak Rai

                    Was not 4 correct for what you shared??

                    • 7. Re: Count record if two dimensions are equal
                      Cathi Champion

                      It works with the sample data, but does not work in the larger query I am trying to execute. I cannot share the real data so, as I said, it does not get me the result but it does give me clues to perhaps figure it out. Thanks