2 Replies Latest reply: May 31, 2013 6:14 PM by Stephen Daspit RSS

    count elapsed days in span while exluding custom date list

      Hello! I am trying to count elapsed days between two events while not counting dates appearing in a custom list. The custom date list tracks all non-worked days. Note this is a custom list that doesn't map exactly to business days, so I cannot use any default workday counting functions.

       

      My data (simplified) looks like this:

       

       

      DAYSOFF

      Rep_IdNotWorked_Date
      AA1/1
      AA1/2
      AA1/20
      BB1/3
      BB1/20
      BB1/5

       

       

      CONTACTS

      Client_IdRep_IdContact_Date
      aAA12/31
      bAA1/3
      cBB1/15
      dBB1/10
      eBB1/4

       

      SALES

      Sale_IdSale_DateClient_Id
      11/15a
      21/5b
      31/23c
      41/17a
      51/10d
      61/15c
      71/8a

       

       

      I'm trying to count elapsed working days (by sales rep) between client contact and the most recent sale to that client. I envision something similar to this (extra columns added for clarity):

       

      Client_IdContact_DateMost_Recent_Sale_Date ie- max(Sale_Date)Worked_Days_Elapsed_From_Contact_To_Last_Sale (this is where I'm struggling)
      a12/311/1715
      b1/31/52
      c1/151/237
      d1/101/100
      e1/4--

       

      Sales Reps always map 1:many to clients. Sales always map many:1 to clients. This seems like clean mapping. Conceptually, I can do all of the day counts by hand. I'm struggling with the proper expressions to get Qlikview to do this for me.

       

      My dimension is Client_Id. My only expression will be the elapsed day count that I'm struggling with.

       

      I've tried several tacks: count([Not_Worked_Date] > [Contact_Date]) should get all not worked days after the intial contact. I've tried to subtract all not worked days that are after the most recent sale (subtracting count([Not_Worked_Date] - max([Sale_Date]) from the expression immediately above.

       

      The expression count([Not_Worked_Date] - max([Sale_Date]) is failing on its own, so my subtraction method is not viable.

       

      I've also noticed that I must be assuming that Qlikview is handling something around data relationships that it is not, in fact, doing. The expression count([Not_Worked_Date] > [Contact_Date]) is static across all clients; I expect it to be variable

       

       

      So yeah... this is complicated. I'm new-ish to QV, but trying my best. Please help!