0

Retrieving the past 14 working days dates

So, I am creating fluid charts on our dashboard.  I am having difficulty creating hidden fields that will have the specific #'s to work with. I want these fields to be calculated based on working day dates.  For instance, I want Day 1, Day 2, Day 3, Day 4, Day 5, with Day 5 being today.  So, the remaining days should be:

Day 1 = 2/01, Day 2 = 02/02, Day 3 = 02/05, Day 4 = 02/06, Day 5 = 02/07.

 

An example code of what I would attempt is

let x := date(today() - workdays(1))  # I know this is not the correct syntax.  However for Day 4, I want it to be -1 working day from today.

count(select Leads where 'Lead Student Type' = 1 and 'Lead Entered Date' =x)

3 replies

null
    • Fred
    • 9 mths ago
    • Reported - view

    Here is one possibility:

    The code is:

    5DaysBefore
switch true do
case weekday(Date) <= 4:
    Date - 7
default:
    Date - 5
end

4DaysBefore
switch true do
case weekday(Date) <= 3:
    Date - 6
default:
    Date - 4
end

etc...

    If you create a date field called Date then you can try it out.

    • Dave_Irving
    • 9 mths ago
    • Reported - view

    Hey Fred, would you explain the logic and syntax behind this?  That way I can learn and may be able to replicate similar code in the future.

    • Fred
    • 9 mths ago
    • Reported - view

    Here is another way to do it:

    1DayBefore

switch true do
case weekday(Date) <= 0:
    Date - 3
default:
    Date - 1
end

    Then the next field is:

    2DaysBefore

switch true do
case weekday('1DayBefore') <= 0:
    '1DayBefore' - 3
default:
    '1DayBefore' - 1
end

    As you can see it is the same code but it changes which date field it looks at. You can just change the field that is used to check.

    I use the switch statement as I started off by checking to see the weekday() of the date so I can go back and skip weekend dates. But I found out that they all used the same subtracted number so I then have to use this modified switch statement to allow me to do functions in a case.

    Or another way is to get the date from your raw data. In your raw data do you only have dates that are weekdays? Do you have every weekday of the year in your data? If you don't wouldn't this code break?

Login to reply

Content aside

  • 9 mths agoLast active
  • 3Replies
  • 40Views
  • 2 Following