0

Calculate Cumulative Time with Code

Hello everyone, I need a piece of code to calculate the continuous days of placing orders for sales (using weeks as the time interval). For example, today is Friday, and if I have orders from Monday to Thursday, then if there is an order today, it will return "5 consecutive days of orders". Otherwise, if there is no order today, it will show "4 consecutive days of orders", and tomorrow it will display "0 consecutive days", and the accumulation will start again. The count resets every week. I've written the code I've tried below. Thanks in advance!

let Monday := date(year(today()), month(today()), day(today()) - weekday(today()));
let Sunday := date(year(today()), month(today()), day(today()) - weekday(today()) + 6);
let dates := ((select 'Order')['Order Time' >= Monday and 'Order Time' <= Sunday] order by 'Order Time');
let cumulativeDays := 0;
let currentStreak := 0;
let previousDate := null;
let today := now();
let hasTodayOrder := false;
for date in dates do
    if previousDate = null then
        previousDate := date.'Order Time';
        currentStreak := 1
    else
        let daysDiff := days(date.'Order Time', previousDate);
        if daysDiff = 1 then
            currentStreak := currentStreak + 1
        else
            cumulativeDays := cumulativeDays + currentStreak;
            currentStreak := 1
        end;
        previousDate := date.'Order Time'
    end;
    if format(date.'Order Time', "YYYYMMDD") = format(today, "YYYYMMDD") then
        hasTodayOrder := true
    end
end;
if hasTodayOrder then
    cumulativeDays := cumulativeDays + currentStreak
else
    let daysToToday := days(today, previousDate);
    if daysToToday = 1 then
        cumulativeDays := cumulativeDays + currentStreak
    else
        cumulativeDays := cumulativeDays + currentStreak;
        currentStreak := 0
    end
end;
   cumulativeDays

4 replies

null
    • Fred
    • 3 wk ago
    • Reported - view

    I am probably missing something in my suggestion but couldn't you do something like:

    let thisWeekOrders := (select Orders where Week = 12);
let weekDayArray := unique(thisWeekOrders.WeekDay);
count(weekDayArray)

    You would need to create two new formula fields in Orders:

    1) called Week and uses the week() command

    2) called WeekDay and uses the weekday() command

    Then you can quickly find the Orders that match the current week, line 1.

    Line 2, finds the unique number of days that is in the week.

    Line 3, counts the number of days found.

    With a data set of:

    The formula returns 4.

    You can swap out 12 for week(today()) so it will always show the latest week.

      • gold_cat
      • 3 wk ago
      • Reported - view

       You’re right, Fred. You got the job done with as little code as possible. Thank you!

      • Fred
      • 3 wk ago
      • Reported - view

      It doesn't exclude weekends and doesn't take into account years. If you have multiple years then you can use the yearweek() command.

      • gold_cat
      • 3 wk ago
      • Reported - view

       Thanks for reminding my friend.

Login to reply

Content aside

  • Status Answered
  • 3 wk agoLast active
  • 4Replies
  • 37Views
  • 2 Following