Forum Discussion

MichaelNapolita's avatar
MichaelNapolita
Qrew Trainee
6 years ago

Remove dates from duration if they are contained within that duration

Hello

I am improving my monthly sales forecast chart - essentially it takes the gross sales from the beginning of the month up to today and divides by how many weekdays have passed so far (giving average sales per day) and then multiplies by the total number of sales days in the month giving the forecasted monthly sales.

I would like to also exclude holidays without having to manually edit them monthly.  I would also like it to know on which day the holiday is (for example this month July 4th has already past so I'd like it removed when calculating the average sales per day). 

I have created app wide "variables" for each holiday but I am having difficulty removing them from my range (since its not a fixed range), these variables haven't been used in the formula below.

This is what the formula currently looks like (the -1 is to adjust for the 1 holiday this month, but I'd like this to be referenced through variables instead):

([Total Sales Order Revenue]/[Weekdays Month To Date])*([Total Weekdays Current Month]-1)

Here are my fields:

[Weekdays Month To Date] 
WeekdaySub((Today()+ Days(1)),(LastDayOfMonth(AdjustMonth(Today(),-1))))

[Total Weekdays Current Month]
WeekdaySub((AdjustMonth(FirstDayOfMonth(Today()),+1)),(AdjustMonth(LastDayOfMonth(Today()),-1)))


Thank you in advance for any help / advice and let me know if I am not explaining clearly. thanks!!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Can you tell us about your tables and relationships? How are you getting at the
    [Total Sales Order Revenue]
  • MichaelNapolita's avatar
    MichaelNapolita
    Qrew Trainee
    6 years ago
    Thanks for responding so quickly Mark and thanks for your contributions - I don't know how you find the time but your comments on many things have been very helpful to me

    I'm not sure how to simplify this and explain clearly but I'll do my best:
    I have about 30 tables but I believe only 4 will be relevant here: "Sales Orders" , "Sales Order Line Items" , "Purchase Lots", and  "Purchase Lot Line Items" 

    Relationships :
    Many Sales orders belong to one Sales Order Line Item
    Each Sales Order has many Sales order line items
    Many Sales order line items belong to one Purchase Lot Line Item
    Each Purchase Lot has many Purchase lot line items
    Many Purchase lot line items belong to one Purchase Lot
    Each Purchase Lot Line Item has many Sales order line items

    Essentially a Purchase Lot is created with many purchase lot line items. A Sales Order is created and part or all of a Purchase Lot Line Item is then added to the sales order making it a Sales Order Line Item. 


    [Total Sales Order Revenue] is a field in the Sales Order table - type: Currency(summary) which sums the Sales Order Line Item assigned values, in addition it has a numeric formula to remove costs 
    Not sure if this is applicable but here's the relationship for the field:

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Hmmm,
    Let me ask this question.

    You are trying to calculate the # of Working Days in a particular month not counting holidays.

    Which month?

    Ie is is the month if the date of the Sales Order? Or if not how can you succinctly say "which month"?
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    6 years ago
    To back up a step to the question in the title: You can use simple binary operators against date values. A simple example would be

    [Weekdays Month To Date]

    var number holidays = if(FirstDayOfMonth(Today()) < [Independence Day] and [Independence Day] < Today(),1,0)

    WeekdaySub((Today()+ Days(1)),(LastDayOfMonth(AdjustMonth(Today(),-1))))-$holidays


    This strikes me as one of those situations where we can compromise theoretical scalability a bit given our known real-world constraints. Are there any months with multiple federal holidays? In other words it looks like it would be clunky to expand an expression like this to handle more holidays, but since we know there aren't that many of them, and they're fixed over the course of a year, a direct approach like this might be best.

    So the expanded expression looks like

    var number holidays =
    if(FirstDayOfMonth(Today()) < [holiday1] and [holiday1] < Today(),1,0)+
    if(FirstDayOfMonth(Today()) < [holiday2] and [holiday2] < Today(),1,0)+
    if(FirstDayOfMonth(Today()) < [holiday3] and [holiday3] < Today(),1,0)+
    etc through 13 or however many you have.

    You'd never write a formula like that if you had 100 holidays, or if there were a variable number, but it's probably fine for this application.

    Here's more information on formula variables, in case that's unfamiliar to you: https://help.quickbase.com/user-assistance/formula_variables.html
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Th track I would like to be on here once I get an answer from Michael is to have a table of holidays where the Key field is the date and then look that up down into some record. Buy I haver to better understand the structure.
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      6 years ago
      For some reason I was assuming he had already got that far although upon another look I see I probably mis-read.

      That'd certainly be the approach. I was thinking to the next step because the post title got my mind moving on that track before I had consumed all the details of the post, how to reproduce something like a Contains() function for a duration of days.

      I'll be interested to see your full solution, with a proper structure - more elegant I'm sure than the hacky way I'd probably have done it. I was thinking just create the holiday date table with a record for each date to exclude, look up each of them into fields in the table we're doing the calculation in, and don't put them on any forms. I end up with a lot of field creep sometimes...
  • MichaelNapolita's avatar
    MichaelNapolita
    Qrew Trainee
    6 years ago
    I'd like it to automatically use the current month (which it currently does it just doesn't exclude holidays) - specifically it uses "Sales Order Pick Up Date" (how I define the day my customer picks up the order) to calculate which date the sales order will count for 

    So to answer your question which month = the current month   (if I make it a fixed month I'll have to update this report every month)

    I'm trying to calculate two things to make this work:
    [Total Weekdays Current Month] # of working days in the month (for current month) excluding holidays
    [Weekdays Month To Date] # of working days from the 1st day of this month until today (for current month) excluding holidays

    I 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    OK, so let's answer the very specific question

    How many holidays are on this month.

    1. Make a table of holidays and enter then next 5 or 10 years of holidays.
    2. Make a calculated checkbox field on the table to flag holidays in the current month.  The formula for that will be

    FirstDayOfMonth([Holiday Date]) = FirstDayOfMonth(Today())

    3. New table with one and only 1 record in it and then block all users even you from adding or deleting records.

    4. Make a relationship back to the Holidays table where the reference field call [Link to Holiday count] is a formula field with a formula of 1.

    5. Make a summary of the # of holidays for the current month.
     
    6. make a relationship similarly from that single Holiday count record down to all Orders and lookup the # of holidays for the current month.