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....
Forum Discussion
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:
- 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/
------------------------------
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);Now this text column will display something like:
var Date PreviousWednesday = $CurrentWednesday - Days(7);
If ([Sent to 1st Level Review] < $CurrentWednesday,
"Week of Wednesday - " & ToText($PreviousWednesday),
"Week of Wednesday - " & ToText($CurrentWednesday)
)
- 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
Qrew Legend
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
------------------------------- Awesome!! thanks everyone for such a quick and great help with this. I really appreciated it.
Best regards,
------------------------------
Sergio Sanchez
------------------------------
