21 Replies Latest reply: Jul 28, 2017 7:20 AM by Sunny Talwar RSS

    Set Analysis Combination

    Cassandra Baqir

      2 requirements:

       

      Requirement 1: combine base expression with: Of those created within the last 6 months, how many touched PROCESS_STEP={'Submitted to Routing'} ?

       

       

      Base expression to identify tickets created within the last 6 months:

       

      =Count({$<PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},PROCESS_STEP={'Ticket Created (Detected)'}>} distinct DEFECT_ID)

       

      Requirement 2: How many DEFECT_ID have a DEFECT_STATUS = Closed within the last 6 months but were created BEFORE 6 months ago?

      Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},DEFECT_STATUS={'Closed'}>} distinct DEFECT_ID)

       

       

      TIA!

        • Re: Set Analysis Combination
          Sunny Talwar

          May be this for Req 1

          ='ERC - Routing: ' & Count({$<DEFECT_ID = p({<PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},PROCESS_STEP={'Submitted to Routing'}>}),

          PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},PROCESS_STEP={'Ticket Created (Detected)'}>} distinct DEFECT_ID)

           

          What is the expected output for Req 2?

            • Re: Set Analysis Combination
              Cassandra Baqir

              I don't know what the expected output is... I will try to write a query against the database to figure it out.

              • Re: Set Analysis Combination
                Cassandra Baqir

                I think the expected output is around 450 for req 2.

                • Re: Set Analysis Combination
                  Cassandra Baqir

                  It turns out getting query uncovered that I was using a field with logic to only look at defects with status = Closed.

                   

                  You helped me get the logic of defects created in the last 6 months using only the min start date for New status and this looks correct:

                   

                  ='Tickets Created: ' & Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

                   

                  Requirement 1: Of those created within the last 6 months, how many have passed through GOV_PROCESS_QUEUE = ERC - Routing? Expected 282:

                   

                  ='ERC - Routing: ' & Count({$<DEFECT_ID = p({<GOV_PROCESS_QUEUE={'ERC - Routing'}>}),
                  DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} distinct DEFECT_ID)

                   

                  Requirement 2: Of those created BEFORE the last 6 months, how many were closed within the last 6 months? I expect 454 here.

                   

                  ='Closed w/i 6 months; Created >6 months: ' & Count({$<DEFECT_ID =
                  p({<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'Closed'}>}),
                  DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"},
                  DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

                    • Re: Set Analysis Combination
                      Sunny Talwar

                      For Req 1, I am getting 276

                       

                      ='ERC - Routing: ' & Count(

                      {1<GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

                      DISTINCT DEFECT_ID)


                      Capture.PNG

                       

                      Attached chart will show you the list of DEFECT_IDs which are included in the 276 here.

                        • Re: Set Analysis Combination
                          Cassandra Baqir

                          Thanks - I think this is correct for Req 1 - my data is a little newer.

                            • Re: Set Analysis Combination
                              Sunny Talwar

                              For Req 2, I am getting 423

                               

                              ='Closed w/i 6 months; Created >6 months (expected 454): ' & Count({$<DEFECT_ID =

                              {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) < AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'Closed'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6)"}>} DISTINCT DEFECT_ID)

                               

                              Capture.PNG

                                • Re: Set Analysis Combination
                                  Cassandra Baqir

                                  thank you!

                                    • Re: Set Analysis Combination
                                      Sunny Talwar

                                      No problem at all

                                        • Re: Set Analysis Combination
                                          Cassandra Baqir

                                          Ok, one more wrench. I need to be able to filter these text boxes by REQUEST_REGION and I think using 1 is preventing me from doing that. Is there any reason why it is needed because if I take it out, the numbers still look correct to me.

                                           

                                          ='ERC - Routing: ' & Count(

                                          {1<GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

                                          DISTINCT DEFECT_ID)

                                            • Re: Set Analysis Combination
                                              Sunny Talwar

                                              Yes that is true, I don't know your data model well enough to understand which selections were causing your expression to not work... but if you can find those replace 1 with the specific fields like this

                                               

                                              ='ERC - Routing: ' & Count(

                                              {<Field1, Field2, Field3,.....,GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({<Field1, Field2, Field3,.....,DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<Field1, Field2, Field3,.....,DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

                                              DISTINCT DEFECT_ID)

                                               

                                              Alternatively, if you only want this one selection, you can do this

                                               

                                              ='ERC - Routing: ' & Count(

                                              {1<REQUEST_REGION = p(REQUEST_REGION), GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({1<REQUEST_REGION = p(REQUEST_REGION), DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<REQUEST_REGION = p(REQUEST_REGION), DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

                                              DISTINCT DEFECT_ID)