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
-
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.
Content aside
- Status Answered
- 4 mths agoLast active
- 4Replies
- 47Views
-
2
Following