11 Replies Latest reply: Oct 15, 2014 9:44 AM by Nathan Horn Branched to a new discussion. RSS


      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.






        • Re: Comparisons
          Jonathan Poole

          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)    

          • Re: Comparisons

            Would you use both statements within the outgoing message expression?

              • Re: Comparisons
                Jonathan Poole

                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 .

                  • Re: Comparisons

                    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:

                    Time 2.JPG.jpg

                    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.