Hi Community,
My app is recording the date/time that a vehicle is loaded.
Our sites run 24/7 meaning that there is a night shift and our shift patterns are:
06:00 - 18:00
18:00 - 06:00
My question is:
How would I be able to count all of the orders loaded within the shift patterns?
I would like to be able to:
Count if an order is loaded between the hours of:
06:00 and 17:59
and
18:00 and 05:59
Iโm able to count if the orders have been loaded between 00:00 and 23:59 but could do with breaking these up.
I have a table โDatesโ with a date column [Date] where the table is referencing the table โOrder_Flow_Archiveโ with all of the orders and the timestamp column [Time To Trailer].
I hope Iโve outlined my conundrum in a way thatโs easy to understand, but if not please ask.
Any help with this would be mostly appreciated.
Many thanks in advance,
Chris.
@Chris_Jeal
For 06:00 and 17:59 shift:
COUNT(
SELECT(
Order_Flow_Archieve[Key],
AND(
DATETIME([Date]&" "&[Date].[Time To Trailer])>=DATETIME([Date]&" 06:00:00"),
DATETIME([Date]&" "&[Date].[Time To Trailer])<=DATETIME([Date]&" 17:59:59")
)
)
)
For 18:00 and 5:59 shift:
COUNT(
SELECT(
Order_Flow_Archieve[Key],
AND(
AND(
DATETIME([Date]&" "&[Date].[Time To Trailer])>=DATETIME([Date]&" 18:00:00"),
DATETIME([Date]&" "&[Date].[Time To Trailer])<=DATETIME([Date]&" 23:59:59")
),
AND(
DATETIME(([Date] + 1)&" "&[Date].[Time To Trailer])>=DATETIME(([Date] + 1)&" 00:00:00"),
DATETIME(([Date] + 1)&" "&[Date].[Time To Trailer])<=DATETIME(([Date] + 1)&" 5:59:59")
)
)
)
)
Many Thanks, @LeventK
Brilliant work!
The expression worked fine after a couple of tweaks, I think I may not have asked the question in the best way but you still came up with the base I needed.
The expressions I settled on are:
Days:
COUNT(
SELECT(
Order_Flow_Archive[Reference],
AND(
date([Time To Trailer])=[Date],
TIME([Time To Trailer])>=" 06:00:00",
TIME([Time To Trailer])<=" 17:59:59"
)
)
)
Nights:
(This I had for some reason to break up into two but nevertheless got the answer I was seeking)
COUNT(
SELECT(
Order_Flow_Archive[Reference],
AND(
date([Time To Trailer])=[Date],
TIME([Time To Trailer])>=โ18:00:00โ,
TIME([Time To Trailer])<=" 23:59:59"
)
))
&
count(
select(
Order_Flow_Archive[Reference],
AND(
date([Time To Trailer])=[Date]+1,
TIME([Time To Trailer])>=" 00:00:00",
TIME([Time To Trailer])<=" 05:59:59"
)
)
)
Thanks again, have a great Christmas and a Happy New Year.
Best,
Chris.
Hi Community and @LeventK !
Prompt me, please, where need to insert this or like this expression to get a single aggregated value (total count of records or sum of values). I want to use aggregated value in KPI on the dashboard as single digit but only can get the list of records.
Iโm sorry, but I couldnโt find such information on the forum.
Many thanks!
Arnold.
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |