Sum a field by week

[TimGoff]

Hello,

I have two fields - date & duration

I would like to have a table in the bottom of my report (the report shows each line) showing the duration summed by week.

Is this possible?

Many thanks
Tim

 

[dgillz]

Yes, when you group by a date or date-time field, Crystal has a drop down list indicating how often you want the data grouped. Pick weekly.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Madawc]

If you group, you can get totals for each week along with the detail records.

If you want all of the detail lines, and then a set of weekly totals, you could do it using running totals, but that would be a lot of work. Better to put a subreport in the report footer and have the details suppressed, with just the totals. Depending on your data, this might be best done by a crosstab.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[lbass]

It sounds like you want a summary at the end of the report. I would insert a crosstab in the report footer. Use {table.date} as the row field and while it is highlighted->group options->select print on change of week. Then add sum of duration as the summary field.

-LB

 

[TimGoff]

Cross tab does it perfectly - thanks everyone for your help
Tim

 

[TimGoff]

Hello
The cross table is working great for the durations but what i also want is percentages too. I want subtitled duration grouped by week - and as a percentage of the total duration

So i have



Date(by week) Subtitled Duration %age
Total duration 100%

The only option I have though is subtitled duration as a percentage of itself.

Is this possible?

Thanks in advance
Tim

 

[lbass]

What version of CR are you using?

-LB

 

[lbass]

If you are using a higher version of CR, like CR XI, you can create a formula:

{table.duration} % sum({table.duration})

Insert this as a summary field, choosing sum as the summary. Then in preview mode, select the inner and total cells and click on the % icon in the toolbar.

-LB

 

[TimGoff]

Hi - i'm only using CR9 unfortunately.
Is there anything i can do in that ?
Cheers
Tim

 

[lbass]

I am not familiar with CR 9. Did you try the above (which works in 11.0)? Let me know if it doesn't work, and I will suggest a different solution.

-LB

 

[TimGoff]

I've only got 9 - and I need to embed the reports into an application that only supports 9 so wouldn't be able to upgrade unfortunately.
Sorry LB - thanks for your patience.
Tim

 

[lbass]

Tim, you still haven't said whether you tried the formula I suggested. It might work in 9.0--I'm just not sure. You need to tell me.

-LB

 

[lbass]

I guess I should assume it didn't work. Again, I'm not sure how SQL expressions work in 9.0, but if you have the option of using one, try creating the following {%sumall}:

(select sum(table.`duration`) from table)

If you have selection criteria, they would need to be built into the SQL expression.

Then create a formula {@percent}:

{table.duration} % {%sumall}

Add this as a summary field, using sum as the summary.

If the SQL expression doesn't work correctly, you could also return the sum of duration by using "Add Command" as your datasource.

-LB

 

[TimGoff]

Still fiddling actually!
Thanks for your latest suggestion, i'll give it a go...
Cheers
Tim