I need to create a time duration field but based on a schedule. For example, the work schedule is from 8h to 17hrs on weekdays if the order entered for example at 18hrs out of business hours, we dont want to count time duration from 18h, the time field will count duration after 8h for the next available day, so for example if the order was pick up at 9h duration will be only 1 hour.
I have a field for the start date and a field for total duration in queue in seconds, but need to exclude the duration based on above schedule.
Hello @HUIL , I would try creating a start and an end time parameter for to the times of day that you want to measure with. Next you could create a calculated field that will determine if the pickUpTime field should be compared to the orderTime field or StartTimeParameter to get the total time for pickup. Try this calculated field with the parameters you created:
ifelse({orderTime} >= ${EndTimeParam}, dateDiff({pickUpTime}, ${StartTimeParam}),
dateDiff({orderTime}, {pickUpTime}))
Let me know if that works!
3 Likes
Hello @HUIL, did my response help you resolve your issue in QuickSight? If so I can mark it as a solution!
Hello! I tested the sample provided with parameters but it didn’t give me the result expected.
I am creating a table with several columns, so I want to have a column/attribute only with the time passed between “in hours” schedule without filtering the other fields on the table, the time output can be in seconds so then I can convert it to time format.
Is there any way to calculate against a static value?
For example I have this order which started afterhours, I have already a column for the total queue duration, but I want a similar column showing the time passed starting 8am that should be 32 minutes as per this example.
For example if the order dequeued at 6 am the column will not show any value.
Hello @HUIL, in my above example where I suggested using parameters to determine the start and end times, you do not have to use those to filter your tables. I think if you used them just to determine the value returned by the calculated field it should work. You may need to add another portion of the ifelse to account for orders that happen before the start time and not just the end time. Then if you want to convert the value to seconds you can do that in another calculated field for a new column or in the return of the ifelse. Something like this:
ifelse({orderTime} >= ${EndTimeParam} OR {orderTime} < ${StartTimeParam}, dateDiff({pickUpTime}, ${StartTimeParam}, "SS"),
dateDiff({orderTime}, {pickUpTime}, "SS"))
Or if you don’t want it to return the number of seconds between if it the order was placed and delivered during the normal time you can simply return the column value you want. Let me know if this helps!
I was able to do the comparison with the hour part only, I had to convert it to date to have it to work, then created additional calculated field as required. Thank you!
1 Like