Discussions

 View Only
  • 1.  Group and filter by "customized week"

    Posted 10-07-2019 17:54
    Edited by Sergio Sanchez 10-07-2019 17:54
    #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
    ------------------------------
    ​​​


  • 2.  RE: Group and filter by "customized week"

    Posted 10-07-2019 18:00
    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
    ------------------------------



  • 3.  RE: Group and filter by "customized week"

    Posted 10-07-2019 18:08
    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
    ------------------------------



  • 4.  RE: Group and filter by "customized week"

    Posted 10-07-2019 23:40
    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/
    ------------------------------



  • 5.  RE: Group and filter by "customized week"

    Posted 10-08-2019 12:09
    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
    ------------------------------



  • 6.  RE: Group and filter by "customized week"

    Posted 10-08-2019 12:24
    Awesome!! thanks everyone for such a quick and great help with this. I really appreciated it. 
    Best regards,

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