Forum Discussion

SergioSanchez1's avatar
SergioSanchez1
Qrew Trainee
6 years ago

Group and filter by "customized week"

#Formulaandfunctions #reportsandcharts #Formulasandfunctions
Hello
I would like to get assistance on how to group and filter records based on particular criteria: workweek from Wednesday to Tuesday. For example, on excel I can do it like this: 

so as you can see, I can count records if the date is between the range established in excel - from Wed to Tue. Basically the need is to group and count records based on customized date ranges. Each record has it own "Sent to 1st level Review date", I just need the count based on those ranges and then show them in Summary or Table reports. 
I hope it was clear enough. Thanks in advanced. 

Regards, 


------------------------------
Sergio Sanchez
------------------------------
​​​
Formula and functions
Formulas and functions
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Ou will need to create a text field which calculates to the choices that you want.  Then use that text field as a Dynamic Filter.

    Post back if you ned help on how to write the formula being very clear how you want the text to read (or maybe you want it similar to the screen shot and if so, at what point if any, do the dates get grouped in "really old" or "really far ahead".

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • SergioSanchez1's avatar
      SergioSanchez1
      Qrew Trainee
      6 years ago
      Thaqnks a lot Mark :)

      Yes, reading them as the screenshot will be ideal. 6 months back will be sufficient for the calculations I am planning. 

      Kind regards,

      ------------------------------
      Sergio Sanchez
      ------------------------------
  • XavierFan's avatar
    XavierFan
    Qrew Cadet
    6 years ago
    A couple different options here:

    1)  Change the global "start of week" for the entire app
    - in App Properties > App date and time > Date options
    - you can set First day of week to "Wed"
    - then your normal grouping in table and summary reports will use the new setting with Wednesday as first day of the week
    - this only works if this Wednesday week setting will work for all the dates in your entire app

    2)  Create a custom formula to display the custom week

    - create a Formula Text field - let's say you call it something like Custom Week Starting Wednesday
    - use this formula (this is assuming that your current First day of week is set to Sunday)
    - use this formula:
    var Date CurrentWednesday = FirstDayOfWeek([Sent to 1st Level Review]) + Days(3);

    var Date PreviousWednesday = $CurrentWednesday - Days(7);

    If ([Sent to 1st Level Review] < $CurrentWednesday,
    "Week of Wednesday - " & ToText($PreviousWednesday),
    "Week of Wednesday - " & ToText($CurrentWednesday)
    )
    Now this text column will display something like:
    - Week of Wednesday - 07-31-2019
    -     Week of Wednesday - 08-07-2019
    etc.

    If you group using this Formula Text column in your table or summary reports - then the grouping will be by week, starting on Wednesdays.

    If you have multiple different custom weeks, then your formula will need to account for them, or you can create separate Formula Text fields for the different weeks.

    ------------------------------
    Xavier Fan
    Quick Base Solution Provider
    http://xavierfanconsulting.com/
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      To add to what my esteemed QSP colleague, Xavier,  posted above, you were looking for a compact field suitable as a Dynamic Filter.

      This formula should return the Wednesday of each period in he format YYYYMMDD, which will sort nicely as a Dynamic Filter.

      var date D = [Approval Date];

      var date Week = FirstDayOfWeek($D- Days(3)) + Days(3);

      var Text YYYY = ToText(Year($week));
      var text MM = PadLeft(ToText(Month($Week)),2,"0");
      var text DD = PadLeft(ToText(Day($Week)),2,"0");

      $YYYY & $MM & $DD

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      markshnier2@gmail.com
      ------------------------------
      • SergioSanchez1's avatar
        SergioSanchez1
        Qrew Trainee
        6 years ago
        Awesome!! thanks everyone for such a quick and great help with this. I really appreciated it. 
        Best regards,

        ------------------------------
        Sergio Sanchez
        ------------------------------