Discussions

Expand all | Collapse all

Formula based on current week

  • 1.  Formula based on current week

    Posted 13 days ago
    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
    ------------------------------


  • 2.  RE: Formula based on current week

    Posted 13 days 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
    ------------------------------



  • 3.  RE: Formula based on current week

    Posted 13 days 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
    ------------------------------



  • 4.  RE: Formula based on current week

    Posted 13 days 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
    ------------------------------