Date Based Equations, Line of Fit, Setting X to mean different units of time

BSwid — Thu, 25 Apr 2019 17:54:54 GMT

With JMP often we are asked to roll up raw data, group it by week, month, quarter, year, etc. Then go look for a trend.

What are the best practices for dealing with longitudinal data?

Data Type
Modeling Type
Format
Maybe tricks in graph builder?

I'm having trouble getting the graphical output to be both executive presentation friendly AND include an equation that makes sense. For example, it's typically with reference for what x represents. Does X represent a date, a growing integer, a quarter, etc.?

When I use a "raw index" (just a integer for whatever frame of refrence, in this case weeks) the equation makes sense, but the graph x-axis labels aren't human readable. Units are going up 47 units per week, but over what time frame?
When I use a date the represents the first day of the week, the x-axis on the graph looks nice but the equation doesn't lend insight. The time frame is clear, but how fast are units increasing?

Here's an example with raw data rolled up to the week. And we are interested in trend based on weekly increment.

New Table( "DateMathExample_RollByWeek",
	Add Rows( 6 ),
	New Script(
		"Source",
		Data Table( "DateMathExample" ) << Summary(
			Group( :First Day of Week, :Raw Fiscal Week Index, :Fiscal Week YYYYWW ),
			Sum( :Units ),
			Freq( "None" ),
			Weight( "None" ),
			Link to original data table( 0 ),
			output table name( "DateMathExample_RollByWeek" )
		)
	),
	New Script(
		"Sum(Units) vs. First Day of Week",
		Graph Builder(
			Variables( X( :First Day of Week ), Y( :Name( "Sum(Units)" ) ) ),
			Elements(
				Points( X, Y, Legend( 5 ) ),
				Smoother( X, Y, Legend( 6 ) ),
				Line Of Fit(
					X,
					Y,
					Legend( 7 ),
					Confidence of Fit( 0 ),
					Equation( 1 )
				)
			)
		)
	),
	New Script(
		"Sum(Units) vs. Raw Fiscal Week Index",
		Graph Builder(
			Variables( X( :Raw Fiscal Week Index ), Y( :Name( "Sum(Units)" ) ) ),
			Elements(
				Points( X, Y, Legend( 5 ) ),
				Smoother( X, Y, Legend( 6 ) ),
				Line Of Fit(
					X,
					Y,
					Legend( 7 ),
					Confidence of Fit( 0 ),
					Equation( 1 )
				)
			)
		)
	),
	New Column( "First Day of Week",
		Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Set Selected,
		Set Values(
			[3628368000, 3628972800, 3629577600, 3630182400, 3630787200, 3631392000]
		)
	),
	New Column( "Raw Fiscal Week Index",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 15, 0 ),
		Set Values( [1, 2, 3, 4, 5, 6] )
	),
	New Column( "Fiscal Week YYYYWW",
		Character,
		"Ordinal",
		Set Values( {"201852", "201901", "201902", "201903", "201904", "201905"} )
	),
	New Column( "N Rows",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 0 ),
		Set Values( [3, 6, 5, 5, 5, 5] )
	),
	New Column( "Sum(Units)",
		Numeric,
		"Continuous",
		Format( "Best", 15 ),
		Set Values( [65, 251, 79, 162, 312, 340] )
	)
)

Re: Date Based Equations, Line of Fit, Setting X to mean different units of time

vince_faller — Thu, 25 Apr 2019 23:19:12 GMT

If you do value labels you can make the axis say anything you want.

Names default to here(1);
dt = New Table( "DateMathExample_RollByWeek",
	Add Rows( 6 ),
	New Script(
		"Source",
		Data Table( "DateMathExample" ) << Summary(
			Group( :First Day of Week, :Raw Fiscal Week Index, :Fiscal Week YYYYWW ),
			Sum( :Units ),
			Freq( "None" ),
			Weight( "None" ),
			Link to original data table( 0 ),
			output table name( "DateMathExample_RollByWeek" )
		)
	),
	New Script(
		"Sum(Units) vs. First Day of Week",
		Graph Builder(
			Variables( X( :First Day of Week ), Y( :Name( "Sum(Units)" ) ) ),
			Elements(
				Points( X, Y, Legend( 5 ) ),
				Smoother( X, Y, Legend( 6 ) ),
				Line Of Fit(
					X,
					Y,
					Legend( 7 ),
					Confidence of Fit( 0 ),
					Equation( 1 )
				)
			)
		)
	),
	New Script(
		"Sum(Units) vs. Raw Fiscal Week Index",
		Graph Builder(
			Variables( X( :Raw Fiscal Week Index ), Y( :Name( "Sum(Units)" ) ) ),
			Elements(
				Points( X, Y, Legend( 5 ) ),
				Smoother( X, Y, Legend( 6 ) ),
				Line Of Fit(
					X,
					Y,
					Legend( 7 ),
					Confidence of Fit( 0 ),
					Equation( 1 )
				)
			)
		)
	),
	New Column( "First Day of Week",
		Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Set Selected,
		Set Values(
			[3628368000, 3628972800, 3629577600, 3630182400, 3630787200, 3631392000]
		)
	),
	New Column( "Raw Fiscal Week Index",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 15, 0 ),
		Set Values( [1, 2, 3, 4, 5, 6] )
	),
	New Column( "Fiscal Week YYYYWW",
		Character,
		"Ordinal",
		Set Values( {"201852", "201901", "201902", "201903", "201904", "201905"} )
	),
	New Column( "N Rows",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 0 ),
		Set Values( [3, 6, 5, 5, 5, 5] )
	),
	New Column( "Sum(Units)",
		Numeric,
		"Continuous",
		Format( "Best", 15 ),
		Set Values( [65, 251, 79, 162, 312, 340] )
	)
);
//making a value labels list
// format is {actual value = shown value}
labels = {};
for(i=1, i<=nrows(dt), i++, 
	insert into(labels, 
		EvalExpr( //this is to keep it an unevaluated expression
			//the Expr() just says to evaluate that part but leave the rest as is
			// we're trying to get 1 = "12/23/2018" to be the first item in the list
			Expr(Column(dt, "Raw Fiscal Week Index")[i]) = 
			// the date is a number so we have to turn it into string
			Expr(short date(Column(dt, "First Day of Week")[i]) )
		)
	);
);
show(labels);
Column(dt, "Raw Fiscal Week Index") << Set Property("Value Labels", 
	labels
);

Hope this helps

topic Date Based Equations, Line of Fit, Setting X to mean different units of time in Discussions

Date Based Equations, Line of Fit, Setting X to mean different units of time

Re: Date Based Equations, Line of Fit, Setting X to mean different units of time