Averaging by hours and minutes

[Oda312]

Good day!
I have data with date and time like this:

Time f1 f2
24-08-2022 10:19:00 436 369
24-08-2022 10:19:01 471 396
24-08-2022 10:19:02 442 386
24-08-2022 10:19:03 439 422
24-08-2022 10:19:04 436 377
24-08-2022 10:19:05 445 377
24-08-2022 10:19:06 440 413
24-08-2022 10:19:07 450 390
24-08-2022 10:19:08 440 416
24-08-2022 10:19:09 453 417
24-08-2022 10:19:10 491 417

I need a macro to calculate an average f1 and f2 for every hour, or every 5 minutes.
Could you, please, help me to solve this problem?

[karolus]

No, you need no Makro

see attached example

pivot_average_grouped_by_minutes.ods

(27.85 KiB) Downloaded 85 times

[Oda312]

karolus, thank you for a reply.
But I have to write a program, that averages data and then uses obtained results on the next steps.
Thank you for an idea with pivot tables. Now I'm trying to make it in macro..

Maybe someone will be faster than me in making code for this task..)

[RoryOF]

If you "have to write a program...", that suggests your problem is a homework task, which the Forum will not do for you; although it will help with specific problems in your attempt.

Information on OpenOffice's date/time handling is at

https://wiki.openoffice.org/wiki/Docume ... _functions

which information should put you on the correct track to start solving your problem.

[Oda312]

I have a problem with programming pivot tables
When I make them handly - it's allright - see pic1 below:



but when I make in by macro - there is another pic2:


The code is next:

Code: Select all

Sub CreateDataPilotTable()
	Dim oSheet 
	Dim oRange 
	Dim oRangeAddress
	Dim oTables 
	Dim oTDescriptor 
	Dim oFields 
	Dim oField 
	Dim oCellAddress As New com.sun.star.table.CellAddress
	
	oRange = ThisComponent.Sheets.GetByName("Sheet1").getCellRangeByPosition(0,0,2,22477)
	oRangeAddress = oRange.getRangeAddress()
	oCellAddress.Sheet = ThisComponent.Sheets.GetByName("Sheet2").Name
	oCellAddress.Column = 0
	oCellAddress.Row = 0
	oSheet = ThisComponent.Sheets.getByName("Sheet2")
	oTables = oSheet.getDataPilotTables()

	oTDescriptor = oTables.createDataPilotDescriptor()
	oTDescriptor.setSourceRange(oRangeAddress)
	
	oFields = oTDescriptor.getDataPilotFields()
	oField = oFields.getByIndex(0)
	oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
	
'	oField=oFields.getByIndex(1)
'	oField.Orientation=com.sun.star.sheet.DataPilotFieldOrientation.COLUMN

	oField=oFields.getByIndex(1)
	oField.Orientation=com.sun.star.sheet.DataPilotFieldOrientation.DATA
	oField.Function=com.sun.star.sheet.GeneralFunction.AVERAGE

	oField=oFields.getByIndex(2)
	oField.Orientation=com.sun.star.sheet.DataPilotFieldOrientation.DATA
	oField.Function=com.sun.star.sheet.GeneralFunction.AVERAGE	

	oTables.insertNewByName("MyFirstDataPilot3", ThisComponent.Sheets.getByName("Sheet2").getCellByPosition(0,0).getCellAddress(), oTDescriptor)

End Sub

How should I change this code - to make a result like pic1?

[Oda312]

I've got it

Code: Select all

oField = oTableDescr.getDataPilotFields().getByName("Data")
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN

from this branch
https://forum.openoffice.org/en/forum/v ... ot#p422996

[Villeroy]

karolus, thank you for a reply.
But I have to write a program, that averages data and then uses obtained results on the next steps.

https://help.libreoffice.org/7.2/en-US/ ... _id7682424

[Oda312]

Villeroy, thank you for a help
But I'm stuck with grouping my pivot table by hours and minutes in macro..
can't use spreadsheetfunctions

Is there any possible macro to repeat this action:
Select cell with date and time in pivot table -> Data -> Group and Outline -> Group - Intervals -> Hours and Minutes?

MacroRecorder gives strange commands with dispatcher - don't understand them..

Code: Select all

	oDocumentModel = ThisComponent
	oDocumentView = oDocumentModel.getCurrentController()
	oDocumentFrame = oDocumentView.Frame

	' the dispatcher service is used to send commands from the 
	' document frame to the underlaying office application
	oDispatcher = CreateUnoService("com.sun.star.frame.DispatchHelper")
	
	Dim mArgs1(0) As New com.sun.star.beans.PropertyValue
	mArgs1(0).Name = "ToPoint"
	mArgs1(0).Value = "$A$13"
	oDispatcher.executeDispatch(oDocumentFrame, ".uno:GoToCell" ,"" ,0 ,mArgs1())

And when i add this code to my code - the grouping doesn't happen.

[Villeroy]

If you can't use spreadsheet functions, why do you use a spreadsheet anyway? Either you write a program or you use a spreadsheet. Most of that macro voodoo on sheets is unproductive nonsense.
Let me guess:
You export csv from a database, import the csv into sheets and then you try to reprogram database functionality?

[Oda312]

Yes, i import the csv into sheets and then try to aggregate data with selection by 1 min, 5 min or 60 min on user form.