cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
rossmiller
Level I

Sum totals by month from date field

Is there a simple way to create sum totals by month? I have a date field such as 01Apr2008 and I would like to tabulate by month.

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
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sum totals by month from date field

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.

View solution in original post

9 REPLIES 9
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sum totals by month from date field

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.
rossmiller
Level I

Re: Sum totals by month from date field

that did the trick, thanks!
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sum totals by month from date field

I think I just came up with a more elegant solution in a similar recent thread. I thought the problem seemed familiar, that I've been digging in to this not long ago. How ephemeral memory is! Glad you've found a solution.

Re: Sum totals by month from date field

Can you post or link to the more elegant solution. I'm trying to do something similiar with "Week-Year"

Thanks.
mpb
mpb
Level VII

Re: Sum totals by month from date field

There is a Week of Year function which assumes weeks begin on Sunday so you may get a week fragment at the beginning and another one (week 53) at the end of a year.

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

Re: Sum totals by month from date field

Right - I'm actually trying to create a new variable that includes YEAR & WEEK of YEAR. The goal is to look at trends by WEEK across YEARS.

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

Re: Sum totals by month from date field

Well you can use the Year function to get a column containing the year. Then you can model by Year and Week. Or you can make a value like your 200940 by creating a new column whose values are Year*100 + Week if that's what you want. There is no JMP format which displays a date as YYYYWW.
matthewh
Level I

Re: Sum totals by month from date field

i am doing something similar to that described by MS (actually working out average yields by month) and haven't yet found a nicer way.

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!
mpb
mpb
Level VII

Re: Sum totals by month from date field

If you can live with month numbers (1-12) and you don't mind creating a summary table of average yields by month, then with a little effort you can do what you want using Overlay Plot.

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.