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_Id | NotWorked_Date |
|---|
| AA | 1/1 |
| AA | 1/2 |
| AA | 1/20 |
| BB | 1/3 |
| BB | 1/20 |
| BB | 1/5 |
CONTACTS
| Client_Id | Rep_Id | Contact_Date |
|---|
| a | AA | 12/31 |
| b | AA | 1/3 |
| c | BB | 1/15 |
| d | BB | 1/10 |
| e | BB | 1/4 |
SALES
| Sale_Id | Sale_Date | Client_Id |
|---|
| 1 | 1/15 | a |
| 2 | 1/5 | b |
| 3 | 1/23 | c |
| 4 | 1/17 | a |
| 5 | 1/10 | d |
| 6 | 1/15 | c |
| 7 | 1/8 | a |
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_Id | Contact_Date | Most_Recent_Sale_Date ie- max(Sale_Date) | Worked_Days_Elapsed_From_Contact_To_Last_Sale (this is where I'm struggling) |
|---|
| a | 12/31 | 1/17 | 15 |
| b | 1/3 | 1/5 | 2 |
| c | 1/15 | 1/23 | 7 |
| d | 1/10 | 1/10 | 0 |
| e | 1/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!
