-
1. Re: Group by with two columns ??
Mark FraserJul 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!)
Cheers
Mark
-
2. Re: Group by with two columns ??
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 Jul 4, 2016 12:30 AM (in response to Ashish Chaudhari)Sorry Ashish Chaudhari for late reply Weekend
Count of All Rescheduled CallsYes 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 .
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 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 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 ifany 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.
-
6. Re: Group by with two columns ??
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 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.
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 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 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 likeIF [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 Jul 4, 2016 5:15 AM (in response to Sandeep Tiwari)1 of 1 people found this helpfulHi 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