This content has been marked as final. Show 2 replies
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:
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)|
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!