10 Replies Latest reply on Jul 4, 2016 5:15 AM by Ashish Chaudhari

    Group by with two columns ??

    Sandeep Tiwari
    Sandeep Tiwari Jul 1, 2016 5:48 AM

      hi All,

       

      I have to create a report so that
      If  a person(Created By) have Rescheduled a particular patient (Record_id)  more than once.
      I.e lets say he has Rescheduled  a patient 3 times then we have to count it as one.

       

      how can we implement it in our report ??

       

      PFA attache TWBX file below.Mark FraserAshish Chaudhari

       

      created by and Record_id are the fields of Activitylogentry table

      • 453 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Group by with two columns ??
          Mark Fraser
          Mark Fraser Ambassador Jul 1, 2016 8:32 AM (in response to Sandeep Tiwari)

          Hi Sandeep

           

          Im away from the office at the moment.

          Pinging myself for Monday (if you haven't had a solution by then!)

          Mark Fraser

           

          Cheers

          Mark

          • 2. Re: Group by with two columns ??
            Ashish Chaudhari
            Ashish Chaudhari Jul 1, 2016 8:42 PM (in response to Sandeep Tiwari)

            HI Sandeep,

             

            Please refer to the below screenshot. I can see that you have 3 person who can schedule the appointments. You are having 5 distinct patients. do I need to use the the "created on" as a date of rescheduling the appointment? Let me know which column do I use as a rescheduling date. In addition to that please tell me the sheet name where you want to look at this output.

            but when I see it other way (refer below screenshot), patient 4 is scheduled by Admin, HL7 and Sahni on 29th June, 2016. My question is how one patient can be scheduled by 3 ppl that to on the same dates?

            Let me know if I have missed something to understand. Request you to add more clarity on this.

             

            Thanks and Regards,

            -Ashish Chaudhari

            • 3. Re: Group by with two columns ??
              Sandeep Tiwari
              Sandeep Tiwari Jul 4, 2016 12:30 AM (in response to Ashish Chaudhari)

              Sorry Ashish Chaudhari for late reply Weekend   
              Count of All Rescheduled Calls

               

              Yes we will use craeted_on as rescheduled date .

               

              You can look into all the sheets where i am counting no of Rescheduled calls
              i.e.'Count of All Rescheduled Calls','Rescheduled calls by agent'.

               

              And data i have provided is test data that's why my problem was not clear .

               

              Untitled.png
              As  you can see in the screen shot record_id (Patient) is getting rescheduled by HL7 two time.
              i want to count it as 1 only whatever the date it is .

              • 4. Re: Group by with two columns ??
                Ashish Chaudhari
                Ashish Chaudhari Jul 4, 2016 1:39 AM (in response to Sandeep Tiwari)

                Hi Sandeep,

                 

                are you looking for something like this?

                 

                I have created a simple calculation as below which is flagging patients who are rescheduled by users.

                 

                Name - Calc_Reschduled Flag

                 

                if COUNTD([Created On])>1 THEN 1

                ELSE 0

                END

                 

                Output

                Input

                 

                Let me know if you are facing issue in implementing this in any of the specific sheets in the workbook. As per that calculations will change.

                 

                Thanks and Regards,

                Ashish Chaudhari

                • 5. Re: Group by with two columns ??
                  Sandeep Tiwari
                  Sandeep Tiwari Jul 4, 2016 1:51 AM (in response to Ashish Chaudhari)

                  Hi Ashish Chaudhari  i Have nothing to do with Created_on in this calculation
                  I just want to count like if

                  any user Rescheduled  a particular patient 3 times then we have to count it as one.  lik in below image agent HL7 has rescheduled Record_id two times so we have to consider as 1 only. We are not going to take care about created_on in this calculation.

                   

                   

                  Untitled.pngMark Fraser

                  • 6. Re: Group by with two columns ??
                    Ashish Chaudhari
                    Ashish Chaudhari Jul 4, 2016 2:23 AM (in response to Sandeep Tiwari)

                    Hi Sandeep,

                     

                    The two records for HL7 that you have highlighted are separated by CreatedOn. Thus I have used it. My Calculation will also give you the same result, in your case. For HL7, patient id - 4 and take the count of Distinct CreatedOn Date. Answer will be 2.

                     

                    Thanks and Regards,

                    Ashish Chaudhari

                    • 7. Re: Group by with two columns ??
                      Sandeep Tiwari
                      Sandeep Tiwari Jul 4, 2016 2:49 AM (in response to Ashish Chaudhari)

                      Ashish Chaudhari  let take the same example

                       

                      User HL7, has Rescheduled patient_id - 4   2 times but i want to just calculate it as one .

                       

                      We have to group it as patient_id and User only.

                       

                       

                       

                      Untitled.png

                      In The above image i want to give you count

                       

                      User               rescheduled_count        rescheduled_expected_count

                      --------               -------                              -------------------------------------

                      HL7                    3                                   2  ---- As HL7 has rescheduled record_id no 4 two times but

                                                                                               we have to consider it as 1 only

                       

                       

                      Admin                2                                   2

                       

                      sahni                 2                                    2

                       

                       

                      Note- If an User is rescheduling particular patient x   Y times then we have to consider as 1 only.

                       

                      May be this time i am clear to you .

                      • 8. Re: Group by with two columns ??
                        Ashish Chaudhari
                        Ashish Chaudhari Jul 4, 2016 3:16 AM (in response to Sandeep Tiwari)

                        Hi Sandeep,

                         

                        Thanks for the detailed explanation.

                         

                        Please find the updated calculation.

                         

                         

                        { FIXED [Created_First Name], [Record Id] : COUNTD([Record Id]) }

                         

                        Please find the attached output below. This is based on the dummy that you have shared.

                         

                        Output

                         

                        Input

                        Let me know if this works for you or any changes are required.

                         

                        Thanks and Regards,

                        Ashish Chaudhari

                        • 9. Re: Group by with two columns ??
                          Sandeep Tiwari
                          Sandeep Tiwari Jul 4, 2016 3:45 AM (in response to Ashish Chaudhari)

                          Hi Ashish Chaudhari Thank you so much it's working!

                           

                           

                           

                          Just one addition here i want to take the count of Records that are Rescheduled
                          That's why i made that calculated field like

                           

                          IF [Reason Type]="Rescheduled"

                          THEN

                          { FIXED [Created_First Name], [Record Id] : COUNTD([Record Id]) }

                          END

                           

                          then it's giving me wrong result i.e -- count for HL7 as 3 Expected 2

                          • 10. Re: Group by with two columns ??
                            Ashish Chaudhari
                            Ashish Chaudhari Jul 4, 2016 5:15 AM (in response to Sandeep Tiwari)

                            Hi Sandeep,

                             

                            Please try this.

                             

                            { FIXED [Created_First Name], [Record Id] : if [Reason Type]="Rescheduled" then COUNTD([Record Id]) End }

                             

                            Let me know abt this.

                             

                            -Ashish Chaudhari

                            1 of 1 people found this helpful