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
- :
- Sum totals by month from date field

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
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 11, 2010 10:23 AM
(1829 views)

I can display the date as only a month-year, such as 4/2008, but I can't use the tabulate function as it does it by day based on the underlying data.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Feb 11, 2010 12:21 PM
(1433 views)

Solution

I am not sure exactly what you want but you could make a month-only variable by adding a new column with the simple formula

Month(:Date) //assuming Date is the name of your date column

This will yield a numeric column with numbers 1-12. These can be recoded (use e.g. Recode in the Cols menu) to Jan-Dec if you prefer that. Use this column with Summary or Tabulate to get monthly statistics.

However, if you want summarize by both month and year then you could also make a Year(:Date) column and then group after both columns.

Alternatively make a a combined column with the formula (choose format m-y to avoid showing the date)

Date DMY( 1, Month( :Date ), Year( :Date ) )

Or by concatenating the month and year columns I described above using the column formula

Char( :Month ) || "-" || Char( :Year )

The last example gives a nominal month-year though, which may or may not be desired.

Month(:Date) //assuming Date is the name of your date column

This will yield a numeric column with numbers 1-12. These can be recoded (use e.g. Recode in the Cols menu) to Jan-Dec if you prefer that. Use this column with Summary or Tabulate to get monthly statistics.

However, if you want summarize by both month and year then you could also make a Year(:Date) column and then group after both columns.

Alternatively make a a combined column with the formula (choose format m-y to avoid showing the date)

Date DMY( 1, Month( :Date ), Year( :Date ) )

Or by concatenating the month and year columns I described above using the column formula

Char( :Month ) || "-" || Char( :Year )

The last example gives a nominal month-year though, which may or may not be desired.

9 REPLIES

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

Feb 11, 2010 12:21 PM
(1434 views)

Month(:Date) //assuming Date is the name of your date column

This will yield a numeric column with numbers 1-12. These can be recoded (use e.g. Recode in the Cols menu) to Jan-Dec if you prefer that. Use this column with Summary or Tabulate to get monthly statistics.

However, if you want summarize by both month and year then you could also make a Year(:Date) column and then group after both columns.

Alternatively make a a combined column with the formula (choose format m-y to avoid showing the date)

Date DMY( 1, Month( :Date ), Year( :Date ) )

Or by concatenating the month and year columns I described above using the column formula

Char( :Month ) || "-" || Char( :Year )

The last example gives a nominal month-year though, which may or may not be desired.

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

Feb 19, 2010 2:33 PM
(1256 views)

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

Feb 19, 2010 2:53 PM
(1256 views)

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

Mar 8, 2010 12:02 PM
(1256 views)

Thanks.

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

Mar 9, 2010 11:45 AM
(1256 views)

There is a Day of Year function which can be used to calculate a different Week of Year value which starts a full 7 day week on the first day of the year. There will be a 1 or 2 day week 53 fragment at the end of the year. That formula is:

Floor( (Day Of Year( :Date ) - 1) / 7 ) + 1

One way or the other you will have a new column of weeks of the year.

Message was edited by: mpb

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

Mar 11, 2010 1:00 PM
(1256 views)

So we'd lke to get something like YYYWW or 200940. The variable needs to be stored as a numeric variable ideally.

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

Mar 12, 2010 7:33 AM
(1256 views)

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

Mar 16, 2010 8:48 AM
(1256 views)

unfortunately, if i want to plot a bar chart with months as the x-axis and there are months with no data, then that month doesn't get plotted at all, so to make sure there's an empty month in my chart i have to create dummy empty rows, and i'm sure there should be a better way than that!

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

Mar 16, 2010 10:33 AM
(1256 views)

Plot Avg Yield versus Month.

Make into a Needle plot via Y Options

Right Click in the plot and choose Line Width Scale > Other > 30 (say)

Turn off Show Points

If you save the script to table or script window it will reproduce the same effect.