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?
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.?
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] )
)
)
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
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