calculate week of year based on start time and end time

[#1]

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

[#2]

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() );

[#3]

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!

[#4 · Accepted solution]

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 ) );

[#5]

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

[#6]

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!

[#7]

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