Balance Stock Summary Report

[KKumar92]

Hi,

I am working on a small report, where I want to display a Balance Stock Report as Follow;

Transaction_Date	Balance_Stock = 0	Balance_Stock != 0	Refill Duration
02/01/2021	04:47:00 PM	-	18 Hours
03/01/2021	-	10:30:00 AM

Please find the sample dataset below that I am working on;

Item	Location	Date & time	Transaction_Date	Transaction_Time	Transaction	Qty	Balance Stock
X	Dek	1/1/2021 12:00:00 AM	01/01/2021	12:00:00 AM	Opening Stock	10	10
X	Dek	1/1/2021 10:00:00 AM	01/01/2021	10:00:00 AM	Sales	(2)	8
X	Dek	1/1/2021 10:30:00 AM	01/01/2021	10:30:00 AM	Stock In	10	18
X	Dek	1/1/2021 11:00:00 AM	01/01/2021	11:00:00 AM	Sales	(2)	16
X	Dek	1/1/2021 11:40:00 AM	01/01/2021	11:40:00 AM	Sales	(1)	15
X	Dek	1/1/2021 12:50:00 PM	01/01/2021	12:50:00 PM	Sales	(1)	14
X	Dek	1/1/2021 2:00:00 PM	01/01/2021	02:00:00 PM	Sales	(2)	12
X	Dek	1/1/2021 4:00:00 PM	01/01/2021	04:00:00 PM	Sales	(2)	10
X	Dek	1/1/2021 6:00:00 PM	01/01/2021	06:00:00 PM	Sales	(2)	8
X	Dek	1/1/2021 6:20:00 PM	01/01/2021	06:20:00 PM	Sales	(1)	7
X	Dek	1/1/2021 7:05:00 PM	01/01/2021	07:05:00 PM	Sales	(1)	6
X	Dek	1/2/2021 9:22:00 AM	02/01/2021	09:22:00 AM	Sales	(2)	4
X	Dek	1/2/2021 10:30:00 AM	02/01/2021	10:30:00 AM	Stock In	10	14
X	Dek	1/2/2021 11:00:00 AM	02/01/2021	11:00:00 AM	Sales	(2)	12
X	Dek	1/2/2021 11:40:00 AM	02/01/2021	11:40:00 AM	Sales	(1)	11
X	Dek	1/2/2021 12:50:00 PM	02/01/2021	12:50:00 PM	Sales	(1)	10
X	Dek	1/2/2021 2:00:00 PM	02/01/2021	02:00:00 PM	Sales	(3)	7
X	Dek	1/2/2021 4:00:00 PM	02/01/2021	04:00:00 PM	Sales	(2)	5
X	Dek	1/2/2021 4:08:00 PM	02/01/2021	04:08:00 PM	Sales	(3)	2
X	Dek	1/2/2021 4:47:00 PM	02/01/2021	04:47:00 PM	Sales	(2)	0
X	Dek	1/3/2021 10:30:00 AM	03/01/2021	10:30:00 AM	Stock In	15	15

I have used the below formula to calculate the time when the Balance Stock = 0,

=aggr(if(Sum(Balance_Stock)=0, Transaction_Time), Transaction_Time)

However, I am finding it difficult to calculate the transaction time of the next Stock In.

Please advise how I could achieve the above requirement.