KPI Management

AppSheet AppSheet Q&A
1

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.

2X_0_0f9cf6ca2dd8736f47cc79525a2d7a72c26318a2.png
2X_0_0f9cf6ca2dd8736f47cc79525a2d7a72c26318a2.png258×280 68.9 KB

2

@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")
			)
		)
	)
)
3 Likes
3

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.

1 Like
4

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.