1 2 Previous Next 28 Replies Latest reply on Feb 14, 2019 1:41 AM by Mihai Constantinescu

Datediff Calculation with Blending

Nimit Bhardwaj Feb 11, 2019 5:19 AM

I'm trying to run a DATEDIFF calculation to calculate the Average Response time to a Client query.

*1-to-1 blend* *Case Number is distinct in these two sources* * EDIT : 1-to-M Blend. Case number is distinct in the Primary, multiple rows in both the secondary sources *

Primary Data source fields : Case Number, Opened [Date & Time], etc.

Secondary Data source fields : Case Number, First Response [Date & Time], etc.

I want to calculate the average response time, which would mean calculating at the most granular level, the difference of First Response - Opened and aggregating (average) to the highest level.

When trying to calculate this, Tableau is throwing different errors like 'cannot use aggregate and non aggregate together'. I got a valid calculation with "DATEDIFF('hour',MIN([Opened]),ATTR([Secondary Data].[First Response]))" but it did not make sense to me and showed wrong results.

*Tableau Version 2018.2* *Cannot share workbook, sensitive information* * EDIT - Dummy Data attached *

Dummy Data.xlsx 219.5 KB

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?
Like Show 0 Likes(0)

Actions
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?
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
4. Re: Datediff Calculation with Blending

Mihai Constantinescu Feb 11, 2019 6:20 AM (in response to Nimit Bhardwaj)

At first glance:

1. create the join on Case number

2. create duration:

DATEDIFF('minute', ([Opened]),{Fixed [Case Number1]: MIN([First Response])})

I can try with a blend too ...
Like Show 0 Likes(0)

Actions
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?
Like Show 0 Likes(0)

Actions
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)
Like Show 0 Likes(0)

Actions
7. Re: Datediff Calculation with Blending

Mihai Constantinescu Feb 11, 2019 6:54 AM (in response to Mihai Constantinescu)

this is with blend:

with Calculation (duration) = DATEDIFF('minute',MIN([Opened]),MIN([Secondary One (Dummy Data)].[First Response]))
Like Show 0 Likes(0)

Actions
8. Re: Datediff Calculation with Blending

Nimit Bhardwaj Feb 11, 2019 11:17 PM (in response to Mihai Constantinescu)

I tried this and it seems to be doing the trick. However, the view I want should show a single value of Average Response Time per Case.
Would a calculation like sum([Calculation])/COUNTD([ Case Number]) do the trick?
The view right now looks like this with the original data (in hours) :
Like Show 0 Likes(0)

Actions
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.
Like Show 0 Likes(0)

Actions
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]))
}
Like Show 0 Likes(0)

Actions
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?
Like Show 0 Likes(0)

Actions
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]))
}
Like Show 0 Likes(0)

Actions
13. Re: Datediff Calculation with Blending

Nimit Bhardwaj Feb 12, 2019 3:23 AM (in response to Mihai Constantinescu)

This seems to be giving me a very high number when I aggregate this using AVG, even more than the highest value. See below :
Like Show 0 Likes(0)

Actions
14. Re: Datediff Calculation with Blending

Mihai Constantinescu Feb 12, 2019 3:31 AM (in response to Nimit Bhardwaj)

e.g. CS0001200: avg(342+ 400 + 1418) = 720
and here is sum vs LOD avg
Like Show 0 Likes(0)

Actions