Forum Discussion

JoshuaMcAfee's avatar
JoshuaMcAfee
Qrew Member
10 years ago

field day of week formula

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.

Forms
Formulas and functions
Relationships
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 years ago
    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.
  • JoshuaMcAfee's avatar
    JoshuaMcAfee
    Qrew Member
    10 years ago
    So where would I put these formulas as I am building it out. Sorry still a bit new to the platform.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 years ago
    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.
  • JoshuaMcAfee's avatar
    JoshuaMcAfee
    Qrew Member
    10 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 years ago
    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.