6 Replies Latest reply on Jul 28, 2018 8:38 PM by Ankit Bansal

    Count rows based on multiple conditions

    Eva K

      I have a data set similar to shown below, I want to count distinct companies based on following criteria:

       

      Countd(Company)

      If

      order status is for that Company is not 'Canceled'

      and

      If Order Status for that Company are  <50% of total order count for that Company are in (Waiting, Pre-Processing,Ready, Shipped)

      e.g. If Company = 'Apple' has 5 orders, with NO 'Canceled' order status and order status are < 50%  of 5 in (Waiting, Pre-Processing,Ready, Shipped), then I want to count 'Apple'

      But, I don't want to count 'Peach' because the  it doesnt have any 'Canceled' order but order status for Peach is > 50% in ((Waiting, Pre-Processing,Ready, Shipped)

       

      Company     Orderid     Status

      Apple          1               Waiting

      Apple          2               Ready

      Apple          3               New

      Apple          4               New

      Apple          5               New

      Peach          6               Waiting

      Peach          7               Pre-Processing

      Peach          8               New

      Peach          9               Ready 

        • 1. Re: Count rows based on multiple conditions
          Soham Wadekar

          Hi Eva,

           

          You will have to create a couple of calculated fields as follows:

          -> This will count total number of records for the given statuses

          and

           

          2. This will perform the CountD on Company

          I wasn't able to get any results for the dummy data as the condition might not have met. So plz check the condition with live data and let me know if this works.

           

          Thank you,

          Soham

          • 2. Re: Count rows based on multiple conditions
            Eva K

            Hi Soham,

             

            Thank you so much but after reading your solution I realized that there is a small correction in my condition.

             

            Countd(Company)

            If

            order status is for that Company is not 'Canceled'

            and

            If Order Status for that Company are  <50% of total order count for that Company are in (Waiting, Pre-Processing,Ready, Shipped)

            e.g. If Company = 'Apple' has 5 orders, with NO 'Canceled' order status and order status are < 50%  of 5 in (Waiting, Pre-Processing,Ready, Shipped), then I want to count 'Apple'

            But, I don't want to count 'Peach' because the  it doesnt have any 'Canceled' order but order status for Peach is > 50% in ((Waiting, Pre-Processing,Ready, Shipped)

             

            Company     Orderid     Status

            Apple          1               Waiting

            Apple          2               Ready

            Apple          3               New

            Apple          4               New

            Apple          5               New

            Peach          6               Waiting

            Peach          7               Pre-Processing

            Peach          8               New

            Peach          9               Ready  

             

            Hope its not confusing

            • 3. Re: Count rows based on multiple conditions
              Ankit Bansal

              Eva,

               

              Can you elaborate what do you mean by order status >50 or < 50 %

              • 4. Re: Count rows based on multiple conditions
                Ankit Bansal

                Eva,

                 

                Check if this works for you :

                 

                first create a calculation say % order status as (i hope you need distinct count of statuses, You can change if you want):

                and convert this column to dimension

                 

                then create another column say valid company as :

                 

                 

                Put this on your filter shelf.

                 

                In your sample data I am getting only Plum.

                 

                Hope this helps.

                1 of 1 people found this helpful
                • 5. Re: Count rows based on multiple conditions
                  Eva K

                  Awesome Ankit, it worked!! Thank you so much buddy!

                  • 6. Re: Count rows based on multiple conditions
                    Ankit Bansal

                    You are welcome Eva