5 Replies Latest reply on Sep 11, 2018 5:43 AM by Zhouyi Zhang

    how to count number of rows

    Suman Manu

      Hi All,

       

      I have a table as below

       

      Below mention dates are part of 8/5 to 8/11 week, now I want to calculate how many released items during 8/5 week

       

      Date     Status

      8/9/18     Close

      8/9/18     Released

      8/9/18     Released

      8/11/18   Released

      8/11/18   Released

      8/11/18   Close

       

      Calculated field statement as "COUNT(CASE [Status] WHEN 'Released' THEN [Date] END)" and result as 2 but as I need result as 4 (as they are 4 released in a week) . I feel it taking distinct date

       

      How to get it

       

      Thanks

        • 1. Re: how to count number of rows
          Zhouyi Zhang

          Hi, Suman

           

          Try this

           

          Sum(CASE Trim([Status]) WHEN 'Released' THEN 1 else 0 END)

           

          ZZ

          • 2. Re: how to count number of rows
            Deepak Rai

            {COUNT(If Status="Released" Then Status END)}

            • 3. Re: how to count number of rows
              meenu choudhary

              HI Suman,

               

              I used your logic only and i m getting correct result:

               

               

               

               

              Can you please share your workbook,so that we can check why the logic is not working on your workbook?

               

              Anyhow i have used below logic and this is also giving correct result:

               

              Release Item = (if [Status]="Released" then [Number of Records] END)

              • 4. Re: how to count number of rows
                Suman Manu

                Hi All,

                 

                Thanks for replies...

                 

                Let me explain my requirement in detail

                 

                1. we have Order Number, Creation date and Release date columns

                2. Now I need to display how many orders got created in a week and also how many got released in a same week dates

                 

                ex: 8/5 to 8/11 dates consider

                 

                Filtered creation dates to 8/5 to 8/11 and found below 10 PR got created . So creation count should be 10 as below (does not matter with status)

                    

                NumberStatusCreationDateReleaseDate
                PR21Released8/10/20188/21/2018
                PR22Closed8/9/20188/21/2018
                PR23Closed8/9/20188/21/2018
                PR24Closed8/9/20188/21/2018
                PR25Closed8/9/20188/9/2018
                PR26Released8/9/20188/9/2018
                PR27Closed8/8/20188/9/2018
                PR28Closed8/8/20188/9/2018
                PR29Closed8/8/20188/9/2018
                PR30Closed8/8/20188/9/2018

                 

                Filtered released dates to 8/5 to 8/11 and found below 5 PR got released (Does not matter when got created and Released status matters)

                 

                    

                NumberStatusCreationDateReleaseDate
                PR2583Released8/9/20188/9/2018
                PR276Released7/27/20188/6/2018
                PR473Released7/27/20188/6/2018
                PR472Released7/27/20188/6/2018
                PR271Released7/27/20188/6/2018
                PR270Released7/27/20188/6/2018
                PR269Released7/27/20188/6/2018
                PR214Released7/27/20188/6/2018
                PR267Released7/27/20188/6/2018
                PR216Released7/27/20188/6/2018
                PR265Released7/27/20188/6/2018
                PR264Released7/27/20188/6/2018
                PR263Released7/27/20188/6/2018
                PR214Released7/24/20188/6/2018

                 

                Now In tableau I need to use either creation date or release date for date cloumn

                 

                If I use creation date as date column then i get count of creation as 10 (count of numbers) will give result as 10. But not sure how to get release vount as 14.

                 

                Thanks for help in adnvace

                • 5. Re: how to count number of rows
                  Zhouyi Zhang

                  Hi, Suman

                   

                  I use your first data grid as example attached.

                   

                  Below is the steps.

                   

                  1. left join your data to a calendar table multiple times by using different key to map to the calendar

                   

                  2, create calculation fields for different counts.

                   

                   

                  Hope this helps

                   

                  ZZ