How to group chuncks of time seperated by a minimum of 30 mins-Pivot Table

[PitSch]

I have a report in libreoffice calc which contains 2 sheets.
The first one is a data sheet and the second one is a “report” sheet (with pivot tables).

In the first data sheet, there are 3 important columns, the first column has dates, the second column has time stamps and the third column has a product code called “Type”.

I’m trying to create a pivot table that would automatically show per date, per “chuncks of time” seperated by a minimum of 30 mins, the count of type per type.

The trouble comes from how to create the chunks of time. There needs to be at least a 30 mins between 2 time stamp to qualify as a chunck of time.

for example, Data sheet:

Date	Time	Type
17.01.22	9:13	12
17.01.22	9:14	12
17.01.22	9:15	17
17.01.22	9:20	17
17.01.22	9:22	17
17.01.22	12:28	17
17.01.22	12:42	17
17.01.22	15:16	17
17.01.22	15:42	17
17.01.22	15:55	13
17.01.22	15:58	13

The end result in a pivot table should be:

Date	Time-Frame	Type	Count-Type
17.01.22	9:13-9:22	12	2
17.01.22	9:13-9:22	17	3
17.01.22	12:28-12:42	17	2
17.01.22	15:16-15:58	17	2
17.01.22	15:16-15:58	13	2

or

Date	Time-Frame	Type	Count-Type
17.01.22	9:13-9:22	12	2
		17	3
17.01.22	12:28-12:42	17	2
17.01.22	15:16-15:58	17	2
		13	2

Let me know if you have any question.
And thank you in advance.

[keme1]

Add a helper column to “identify the chunks”. For time data in spreadsheets the unit is “day”, and a half hour is 1/48 fraction of a day.

If all the times will be the same date, it is quite simple. Assuming your table starts with the Date heading in A1:
D2: =IF((N(B2)-N(B1))>(1/48);N(D1)+1;N(D1))
Copy down.

[keme1]

… or if you want your “chunk formula” inserted in column C, as per your comment:
C2: =IF((N(B2)-N(B1))>(1/48);N(C1)+1;N(C1))

Still the chunk identifier is a number, not letter as suggested in your comment. That should not be a problem, I guess.

---

The N() function is probably required only for the first row, to discard textual content from the headings so we avoid error messages. So, most likely overdone in the suggested formula…

The N() function returns the “numerical content” of its argument (=0 if the argument is text). This is different from the VALUE() function, which returns the numerical interpretation of a text argument.

[PitSch]

Ok yes. Thank you. This is already a great step forward.
The formula works well except in cases where the date changes.
The increment in those cases doesn’t work.


so if we have:

Date	Time	Formula Chunck	Type
17.01.22	15:16	3	17
17.01.22	15:42	3	17
17.01.22	22:21	4	17
18.01.22	9:42	4	17
18.01.22	10:12	5	17

The jump from one day to the other won’t trigger the incrementation and we’ll stay at the index 4 in this example.


Would you know how to solve that?

[keme1]

@PitSch

Would you know how to solve that?

If those are “proper” date cells, it is a matter of referencing date+time instead of just time of day.

C2: =IF((N(A2)+N(B2)-N(A1)-N(B1))>(1/48);N(C1)+1;N(C1)

[PitSch]

Great, it works wonders. Thank you

[joshua4]

Be very careful if you do anything integrative over these results…if you are looking for amounts of time at some Type rather than, say, charting Type at intervals. Of course, likewise if you are going to use those charts to integrate time-at-Type. Depending on your needs, etc., this could be a nest for sampling errors of the Nyquist type.

[mariosv]

With pivot table, you can make the groups by hours, but not by half an hour.
In the pivot table, go to any cell on time column, and with [Ctrl+F12] (Menu/Data/Group&Outline), select hours to group.

Sample file PivotTableCountByHours.ods (12.4 KB)

[PitSch]

Thank you for your help and for the example .ods

I looked already at the possibility to group per hour within the pivot table features.
Also, in the data sheet I can have a column with a forumla that will create time stamps by increment of 5min or 10 or 15 or 30.
This is the formula I use =FLOOR(B2;TIME(0;30;0)
And then be able to use that in the pivot table.


The issue is the chuncks of time over laps between hours and half hours.
So I need to seperate them between 30 mins minimum.


for example:
Between 9h22 and 12h28, there is at least 30mins of no entry. So everything before and including 9h22 is a chunck.
then 12h28 onward would be another chunck.


But between 9h20 and 9h22 there are only 2 minutes. So they would be part of the same chunck of time.


It would be the same if one entry is 10h10 and the next is 10h38. both would be in the same chunck of time because it’s only 28 mins between the 2 entries.


So I’m looking for maybe a formula that would mark the chunck in a column which I would be using in the pivot table.
Maybe something like 10h38-10h10 = 28 if 10h38-10h10<30 then mark chunck A if not then mark B.
And keep incrementing the mark.


It might look like:

Date	Time	Formula Chunck	Type
17.01.22	9:13	A	12
17.01.22	9:14	A	12
17.01.22	9:15	A	17
17.01.22	9:20	A	17
17.01.22	9:22	A	17
17.01.22	12:28	B	17
17.01.22	12:42	B	17
17.01.22	15:16	C	17
17.01.22	15:42	C	17

[Villeroy]

t74936.ods (30.8 KB)

[PitSch]

That’s a nice attempt
It was almost there.


The formulas are increments of 15 mins instead of 30 mins but this can easily adapted from 1/96 to 1/48.


The column “Sequence” in the file, I think the formula shoud be B3-B2 instead of B3-C2,
so that the formula compares with the previous entry instead of the very first entry.
e.g. comparing 15:55 to 15:42 instead of 15:55 to 15:16


The column “Quarter hour” is a nice touch
Apart from the fact that it’s fixed and not dynamic.
I mean it would be nice if the end time of the chunck would be the actual end time of chunck instead of the start time of chunck minus 15 mins or 30 mins.

[Villeroy]

Database queries can generate tables from other tables.
In the attached database, I copied your data into a table (together with a technical row ID), created an intermediate “Query1”, a final aggregation “Query2” and for pretty printing a report based on Query2.
t74936.odb (8.0 KB)
If anything asks you to convert the “outdated” HSQL database into something “better”, just say “No” this time.
P.S. Again, I made the 15 minutes mistake. Just right-click>Edit Query1 and change 15 to 30
P.P.S.: If you ever add more sequences from other dates to the table, the complete “formula” of Query1 should be:

SELECT "T1"."Date", "T1"."Time", MAX( "T2"."Time" ) AS "Max", "T1"."Type" 
FROM "Tabelle1" "T1" JOIN "Tabelle1" "T2" 
  ON DATEDIFF( 'minute', "T1"."Time", "T2"."Time" ) <= 30
  AND "T1"."Date" = "T2"."Date"
GROUP BY "T1"."Date", "T1"."Time", "T1"."Type"

[PitSch]

Thank you, this is promissing.
It’s just that Database queries are a bit out of reach of my current knowledge.
But thanks for the effort.