-
1. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 11, 2019 1:46 AM (in response to Nimit Bhardwaj)A bit harder without a workbook (can't you annonymise a small sample?) ... 1. can you join those 2 datasources (not blend) in the metadata panel? 2. can a Case number contain multiple opening times, are you showing per customer? Could you show a snapshot of Case Number, Open Time, First Response and perhaps the number you are getting with your calc?
-
2. Re: Datediff Calculation with Blending
Nimit Bhardwaj Feb 11, 2019 2:01 AM (in response to Mihai Constantinescu)Two reasons why I cannot use a join (I think?) :
1) I have another secondary source which has multiple rows for each case number. Also, measures in these multiple rows for each case number are different so I cannot use a MIN LOD expression to get rid of the duplication. Can I join this source and blend that one maybe? Never tried this combination, don't know if it is possible.
2) This secondary source does not have a Case Number field by default. There's another field called Case which is of the format "Case: Case Number", so I perform a custom split before I blend here. I'm not aware of the procedure to do a split while joining. Is it possible?
-
3. Re: Datediff Calculation with Blending
Nimit Bhardwaj Feb 11, 2019 5:15 AM (in response to Mihai Constantinescu)* Attaching annonymized data file. Color coded yellow to linking field and green for the datediff calculation fields. *To answer your other questions,
2) A case number cannot contain multiple opening times. Opening time field is in the primary source, hence distinct.
But it can have multiple First Response times since a row is created every time a Case is put in the 'Waiting for L1' state. Hence, I want to use the minimum of this field to calculate the first response time.
-
Dummy Data.xlsx 219.5 KB
-
-
4. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 11, 2019 6:20 AM (in response to Nimit Bhardwaj) -
5. Re: Datediff Calculation with Blending
Nimit Bhardwaj Feb 11, 2019 6:36 AM (in response to Mihai Constantinescu)Can you please tell me the join calculation you used here in the secondary source?
Also, I would prefer using blending since the third source has multiple rows for the same Case Number where I need to sum the values (Might cause replication problems).
Can I possibly use these two sources as a join and blend with the third one?
-
6. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 11, 2019 6:41 AM (in response to Nimit Bhardwaj)TRIM(SPLIT([ID],':',2))
and the LOD calculation should be a MIN() if you remove [First Response] from rows/view.
A blend is a join (left join) so it would be same thing ... however, LODs don't work with blend (didn't check in new Tableau versions)
-
7. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 11, 2019 6:54 AM (in response to Mihai Constantinescu) -
8. Re: Datediff Calculation with Blending
Nimit Bhardwaj Feb 11, 2019 11:17 PM (in response to Mihai Constantinescu) -
9. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 12, 2019 2:58 AM (in response to Nimit Bhardwaj)No, that won't work as you are using a MIN from the secondary datasource for First response (doesn't work otherwise) DATEDIFF('minute',MIN([Opened]),MIN([Secondary One (Dummy Data)].[First Response])) ... thus selecting for your calculation the minimum date. I am afraid I don't see an easy way without LODs which are not possible without joining (doesn't work on blend). I still believe that is the better option.
-
10. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 12, 2019 3:08 AM (in response to Mihai Constantinescu)and this should do it:
{ FIXED [Case Number] :
AVG(DATEDIFF('minute', [Opened],[First Response]))
}
-
11. Re: Datediff Calculation with Blending
Nimit Bhardwaj Feb 12, 2019 3:10 AM (in response to Mihai Constantinescu)Yeah, I see that. I did switch to joins before my previous reply. Did a left join using the join calculation you gave. How can I get the average now?
-
12. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 12, 2019 3:13 AM (in response to Nimit Bhardwaj)see above
{ FIXED [Case Number] :
AVG(DATEDIFF('minute', [Opened],[First Response]))
}
-
13. Re: Datediff Calculation with Blending
Nimit Bhardwaj Feb 12, 2019 3:23 AM (in response to Mihai Constantinescu) -
14. Re: Datediff Calculation with Blending
Mihai Constantinescu Feb 12, 2019 3:31 AM (in response to Nimit Bhardwaj)