topic Re: calculate week of year based on start time and end time in Discussions

calculate week of year based on start time and end time

Anonymous — Mon, 02 Jul 2018 17:14:48 GMT

Hello all,

I am working on a test that needs me make a calendar of week based on two columns that I have in dat table. By doing so, uses will be able to select different weeks of year to see the trend. Does anyone know the better way to achieve it?

Thanks,

Winnie

Re: calculate week of year based on start time and end time

txnelson — Mon, 02 Jul 2018 17:19:24 GMT

You may want to use a Calenar Box() to let them select one of the days in the week they want, and then to use that as your input

Names Default To Here( 1 );
New Window( "Calendar Box Example", Calendar Box() );

Re: calculate week of year based on start time and end time

Anonymous — Mon, 02 Jul 2018 17:29:35 GMT

hi Jim,

Thank you so much for your quick reply. I am wondering is there anyway that be able to summary the start time and end time into week of year and then create filters based on that summary table, so user can click on different weeks of a year to check the different trend?

I really appreciate your help!

Re: calculate week of year based on start time and end time

txnelson — Mon, 02 Jul 2018 17:54:48 GMT

Are you looking for something like this?

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Aircraft incidents.jmp" );

dt << New Column( "Work Week", numeric, modeling type( ordinal ), formula( Week Of Year( :event date ) ) );

Current Data Table() << Data Filter(
	Location( {175, 175} ),
	Mode( Show( 1 ), Include( 1 ) ),
	Add Filter(
		columns( :Work Week ),
		Where( :Work Week == {1, 2, 3, 4, 5, 6, 7, 8} ),
		Display( :Work Week, Size( 160, 225 ), List Display )
	)
);

Oneway( Y( :Total Fatal Injuries ), X( :Location ) );

Re: calculate week of year based on start time and end time

Anonymous — Mon, 02 Jul 2018 18:27:54 GMT

That is exactly what I want! Thank you so much for your amazing help!

Re: calculate week of year based on start time and end time

Anonymous — Mon, 02 Jul 2018 21:23:01 GMT

Hi Jim,

I am sorry to bother you again.

The format of date column in my data table is look like: 4/17/18 2:32pm.

When I use week of year() function, there is an error message showing :

"Cannot convert argument to a number [or matrix] 1 times At rows: 2 Operation: Week Of Year, Week Of Year/*###*/(:Test Finish time, 1)
Formula evaluation errors have been ignored"

I guess I can't use week of year() function when the column contains time. Could you give me some deriction about how to solve this problem?

Thanks a lot!

Re: calculate week of year based on start time and end time

txnelson — Mon, 02 Jul 2018 21:45:25 GMT

The issue is that your time column is a character column, not a numeric column. You need to convert your date column to a numeric Date/Time column. Here is a link to a previous discussion that will lead you through that

https://community.jmp.com/t5/Discussions/convert-h-mn-in-mn/td-p/61641