I'm not sure how to go about this. I have a database of completed orders and need to find the rolling percentage of how many completed in the past seven days were completed within 168 hours. Here's some of the fields I'm working with, feel free to ask me about anything pertaining to the existence of other types of fields this may need.
start_time (date & time)
end_time (date & time)
BM19 is a bookmark that has all completed orders selected, not active.
I am assuming you wish for an expression that would plot this on a chart, with date as the dimension?
In that case, create a chart with Date (End_Time) as a dimension, set it so that it does not show nulls (this will remove the need to reference the bookmark in the expression, but you can still do it that way if you wish!)
You will then need to use the above() function and rangesum() in the expression
Above() returns values in the "rows" above the date that you are looking at (imagine as if your chart was a table)
and Rangesum() takes those values and sums it.
Rather than an if statement, the statement -(end_time - start_time < 7) will return 1 if the time taken was <7 days (168 hours)
This will sum the number of tasks completed within 168 hours.To keep things simpler, and calculations quicker, I recommend calculating a "timeTaken" column in the script or a flag to show when the tasks were completed in the target.