Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Date Based Equations, Line of Fit, Setting X to mean different units of time

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 25, 2019 10:54 AM
(1629 views)

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] )
)
)
```

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Vince Faller - Predictum

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Vince Faller - Predictum