Forum Discussion

KimCameron2's avatar
KimCameron2
Qrew Trainee
5 years ago

Formula based on current week

OK...so this may be lengthy so I apologize up front...
I have one table Hours in which I import daily hours worked by reps by shift.

I have two tables that contain my two different kinds of reps - one is OPERATORs(TSR) and the other is VERIFIERSs(TO) - it contains their name and rep ID

I have one table that is Customers (aka sales)

I am wanting to show the SalesPerHour (#of sales with status <> CXL / #ofhours worked) per week without having to enter the date range, which as far as I know is not possible​.  I have a Total SPH (displaying on the rep tables) for each rep (thanks to Mark yesterday) and it looks amazing, but I need to be able to break out the weeks "automatically"
SO.....I have been thinking, which is not always good because I do not come from a computer background, but what if I went in and created a table with preset date ranges (aka my weeks) with formulas based on those and pull the info from the other tables to complete...would that work?  Am I overthinking?  Is that even a possibility?  I am afraid I am lost on this one and it is probably super basic...any help would be so appreciated.

------------------------------
Kim Cameron
Admin/Sales
Vacation Tour & Travel
Conway AR
15013295434
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    This is in fact a non trivial problem,  so not super basic.

    In order to do that division to get at SPH you would need to have a table where the Key field is a concatenation of the Operator and the week.   You could create a formula field on your Hours table to make that field.  The format would depend on how the  Operator appears in the sales table, but suppose it was based on their name, so the format would be like Jane Smith-20200628 where the suffix is the first day of the week.

    You can then run that summary report each week to populate the Employee Weeks table (One this is working, we could work together to Automate this).

    Then you can make relationships down to sales (Customers) and hors to get the total sales and the total hours for each Employee-Weeks.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • KimCameron2's avatar
      KimCameron2
      Qrew Trainee
      5 years ago
      Awesome...I think I am actually "getting this"....so, we can have the key field (concatenation) "created" every week for example since in the world of sales, reps come and go or would I need to add manually for new hires?  What would be ideal is to have the concatenation created from Operators with status "active"...I totally understand what you are saying to do and how it will be done, but I am just unsure about setting conditional criteria on a concatenation field...I am a novice, but eager to learn and am actively doing the University, but I do apologize for repeated questions.

      Thanks in advance.

      ------------------------------
      Kim Cameron
      Admin/Sales
      Vacation Tour & Travel
      Conway AR
      15013295434
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        Good to hear you are making your way through to getting Certified. I would make a formula checkbox field on the Employee Weeks table called [Employee Week exists?]. The formula would be simply

        true

        So it will always be checked.

        Then you will have made your concatenation formula such as 
        List("-", [Employee Name], FirstDayOfWeek([Date Worked])

        Then you will have made the relationship based on that formula field being the reference field on the right side of the relationship between hours worked and Employee Weeks.

        Then on that relationship lookup the value of [Employee Week exists?]

        Now each hourly record knows if it is missing the require parent in Employee Weeks.

        Make a summary report of hours based on Employee Weeks but filter it on where [Employee week exist?] is not checked.

        Then use the more ... button to "copy these record to another table" and copy them to the employee weeks table.

        so the not so automatic way of updating this table is to Subscribe yourself to that report to come say 5 days week and when you receive the subscription, run the report and copy the records across to that Employee weeks table.  

        There are ways to automate that but that can come later.


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------