Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

How can Icalculate number of days worked each week based on data entered in another field?

I am trying to figure out how to calculate the number of days worked each week based on a another field greater than 0. I have three fields, date field [Prospecting Date], numeric field [# of Real Estate Conversations], and a text formula field [Day of Week]:
Case(DayOfWeek([Prospecting Date]), 0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday")

I want another field that calculates the number of days worked during each week based on if there has been a number entered in the [# of Real Estate Conversations] field. So if I have 7 conversations on a Sunday (11/5/17) and 0 conversations on a Monday (11/6/17), the number of days worked for that week would be 1. Then if I add 3 conversations for Tuesday (11/7/17) the [Number of Days Worked per Week] would be 2, and so forth. Is this possible?
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    You would need to summarize qualifying days (days that have a conversion) up to some parent record, maybe a weeks table, or a months table.

    Depending on the level of details you need and the historical reporting needed you might need some joined tables.

    What are some of the end results you are looking to do with that "# of Days Worked"?
    What reports?  What time frame?  Historical needed, or just a rolling total?