Sub summaries on the date field

[nickelsto]

I would like to create a report that summarizes the date field. I would like the date field to be in monthly increments.

Can a Date field be summarized with a calculation or script so that sub-summary reports are run for weekly, bimonthly, monthly, and quarterly sub-summaries?

[-Queue-]

I believe you would need additional calculation fields for each possible group of data. For weekly, you could use a calc of WeekOfYear(datefield). For bimonthly, Round( Month(datefield)/2, 0 ). For monthly, Month(datefield). And for quarterly, Round( Month(datefield)/3 + .4, 0 ). Then you can summarize each report by the desired calculation field.

[nickelsto]

ok, excellent, I looked at these different date calculations and didn't realize that a date field would work in the parenthesis.

I attempted the Month() calc, both with date and text (text work a little better) as the calculation result, and it is still giving me a separate subsummary for each incidence of a new day, instead of summarizing all the records of a single month. What do you think?

[nickelsto]

whoops, i didn't have the sub-summary part summarizing on the new field (month()). Ok, now when I do this, the subsummary field disappears entirely. hmm

[-Queue-]

Your sort must sort by the new field for the summary part to display.

[nickelsto]

Oh yes, of course, thanks. It is starting to work, thank you very much. It is summarizing a few of the months and then summarizing several months into one (for some reason). I'll have to keep tinkering with it, but at least I have a start. Thanks again.

[nickelsto]

Ok, I got it to work.

Now, I am needing help with sorting the month() calc field. It sorts like this: 1, 10, 11, 12, 2, 3, 4, 5, etc. Is there a way to change the sorting to let it know that 10, 11, and 12 are above 9?

[-Queue-]

Make it a number field instead of text. I think you could alternately append zeroes to make all results the same length, e.g. Right( "00" & Month(datefield), 2 ).

[nickelsto]

Ok, that worked, unbelievably helpful. Now, "only" two more issues. The sort doesn't take year into consideration. Should the field calc be different because of this?

The last thing was to know if the result of the month() calc field could be the month name rather than the number.

[-Queue-]

Right( "00" & Month(datefield), 2 ) & "_" & Year(datefield) will take year into account.

If you summarize by name, then it will be in alphabetical order. April will be first, and January will be third. But you could put the date field in your subsummary and format it (in Layout Mode) to display the month name only.

[nickelsto]

Excellent, except that the subsummaries are sorting in the following sequence: 01_2004, 01_2005, 02_2004, 02_2005, 02_2006, 03_2004, etc. Is this fixable?

[-Queue-]

You could either sort by year first and then the calculation or transpose the calc to Year(datefield) & "_" & Right( "00" & Month(datefield), 2 ).

[nickelsto]

Crap, that didn't work, I was hoping it would because you have been so helpful and I am feeling a little lame.

Anyway, I can't sort by year first because I don't have a field that is year only. So I transposed the calculation and it did exactly the same thing, it insists on doing a literal sort. One thought: would changing the datefield format to having the year first, work?

[nickelsto]

oh, that did it! Thank you for your help, it has been great

[-Queue-]

Not a problem. I meant to add a calc field that stores the Year(datefield) and sort by that first. Glad you got it worked out though.