Comparisons

[#1]

I have two tables linked by a single ID. Messages in the first table are incoming messages. Messages from the second table are outgoing. We have more outgoing messages than incoming. I need to condense my outgoing messages based on what messages are within five days of an incoming message and lose the rest. Then I need to figure out the response time between the closest matched dates. I am fairly new to this program and any advice is appreciated.

Thanks,

Nathan

[JonnyPoole]

You could use an IF() statement and check the date differential to ensure its less than 5 days apart.  Qlik's associative data model will ensure the IDs are always matched

if(  [DateOutgoing]-[DateIncoming] <=5 ,   sum( [DateOutgoing]-[DateIncoming]) )

This expression should figure out the minimum DateOutgoing  for each ID.

min ( Total  <ID >  DateOutgoing)    

[#3]

Would you use both statements within the outgoing message expression?

[JonnyPoole]

So in the table on the right try:

1. Adding the expression '[DateOutgoing]-[DateIncoming] '  and see if it works and returns the right day #'s

2. If it works, change it to :   ' if(  [DateOutgoing]-[DateIncoming] <=5 ,   [DateOutgoing]-[DateIncoming] )' and see if that works. 

3. if that works, try disabling any other expression in the chart, it should filter it down has 0 values in expressions (so long as all expression in the chart have zeroes' )  are by default auto suppressed. 

ps:  I'm not sure what context you need the minimum date... that wasn't clear in the first post

pps:  this is a bit of guesswork. its better to have an example to use .

[#5]

I created a table with both received date and outgoing dates.

Then applied if([CommSentDate]-[ReceivedDate] <=5 ,sum([CommSentDate]-[ReceivedDate]))

It appears to duplicate received dates and sent dates. It also does not match up in the table.

The second expression-min (Total<Id> CommSentDate) gave me the following:

The min date would be 1/1/2014 for a starting point. The thing is, I need the closet date from the start of each received message to match up with the closet sent date without confusion of past or future dates. Then I need to calculate what the date/time difference is between each compared date/time to figure out an average response time frame. I work in the medical field and the data I have cannot be shared, unfortunately. 

I figured I'd start at a 5 day window. My hope is that messages are answered within 48 hours.

[JonnyPoole]

Could you post a few rows of representative data that isn't real as well as a picture or of the desired outcome ?  You can type both out in an excel spreadsheet.

[#7]

This would be the desired outcome from the data above.

ID	Received Date	Sent Date	Time Response Within
2700	1/1/2014 11:59:24 AM	1/2/2014 2:55:24 PM	27 hours
2700	2/20/2014 10:16:30 AM	2/21/2014 8:46:32 AM	22 hours
2700	5/26/2014 9:09:01 PM	5/27/2014 8:31:33 AM	23 hrs

I'm not particular about how the time response is presented as long as I can read easily.

ex:

0 days, 23 hours, 22 minutes and 32 seconds

or

23 hours

[JonnyPoole]

Can i ask you to export the chart data to xls ?  It will save me typing it all out from the screenshot and also help you ultimately have an example that you can use with minimal translation back to your environment.

[#9]

Let me know if this does not work for you.

[JonnyPoole · Accepted solution]

Perfect. Thanks

I used this expression to check the interval, locate the shortest interval in each grouping of ReceivedDate and render that value only.  If you have multiple PIDs you may have to add  <PID,ReceivedDate> below where there is only <ReceivedDate> to make the grouping unique to the combination of PID and receiveddate.

if(

  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm') = min ( total  <ReceivedDate>  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm')) ,

  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm')

)