Quick Base Discussions

Expand all | Collapse all

field day of week formula

  • 1.  field day of week formula

    Posted 09-23-2015 16:29

    Hi I am trying to build a sales report/graph. I would like to visualize which day of the week gets the most sales. Within the sales table we have a field title Order Date, that we formatted to have the date and day of the week.

    What I would like is a report/graph that would group all of the sales together by day of the week for example (all the sales of June grouped into Monday, Tuesday, Wed,etc) where Monday would have all 4 Mondays of the month combined, Tuesday the same etc. and then show the total so we know what days get the most sales combined.



  • 2.  RE: field day of week formula

    Posted 09-23-2015 16:35
    The formula for the Day of the Week is

    DayOfWeek([date])

    It will return a number from 0 (Sunday) to 6 (Saturday).

    If you were doing a graph and wanted the day names you would need to have a formula such as

    Case(DayOfWeek([date]),

    0, "0-Sunday",

    1,"1-Monday",

    etc

    6,"6-Saturday")



    The reason that you might want to preface the text with numbers is to get them to sort on a graph by day of week.


  • 3.  RE: field day of week formula

    Posted 09-23-2015 16:39
    So where would I put these formulas as I am building it out. Sorry still a bit new to the platform.


  • 4.  RE: field day of week formula

    Posted 09-23-2015 16:43
    no problem.  Just make a formula text field called Day Of Week and paste in that formula into the formula box.  the field [date] refers to your date field, so you will need to change the name to whatever you are calling your date field.


  • 5.  RE: field day of week formula

    Posted 09-23-2015 16:47
    Thanks where would I call the second part into. When I create the top part by its self it says "you used a text formula but the formula returns a number?


  • 6.  RE: field day of week formula

    Posted 09-23-2015 16:56
    If you wanted to have a just formula numeric field to return just the numbers from 0 to 6, then you would use the formula DayOfWeek([date])

    If you wanted to have a formula text field type to have the words then you would use the formula

    Case(DayOfWeek([date]),

    0, "0-Sunday",

    1,"1-Monday",

    etc

    6,"6-Saturday")

    So, the solution is to change your field type to be a text formula.