Skip to main content
Question

Remove dates from duration if they are contained within that duration

  • July 11, 2019
  • 0 replies
  • 86 views
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!!
      This topic has been closed for replies.

      Can you tell us about your tables and relationships? How are you getting at the
      [Total Sales Order Revenue]
      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:

      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"?
      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
      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.
      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 
      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.
      Terms & ConditionsAccessibility statement