sum of particular amount in a date range

[theamis]

Hi!

I need help with regards to Sum()function. I want to get the sum of an amount of a particular field which falls from a date range (example: monthtodate, yeartodate etc.). How do I do it? I have to place it in detail's section.

sample data:

Productname Date Amount
brandA 4/01/03 500
brandB 4/02/03 200
BrandC 4/01/03 300
BrandA 4/02/03 400
BrandA 5/02/03 400

If I need to get the sum of the amount of all BrandA in April, what must I do? I am using crystal report 7
Thanks!

 

[dgillz]

Try this:

If {DateField} in Date(2003,4,1) to date(2003,4,30) then {AmountField} else 0

Then do a simple sum on this formula.

Alternatively, exclude all records from your report which do not fall in the month of April with a record selection formula.

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

http://www.trianglepartners.com

 

[synapsevampire]

You might Group by the date->Month (Insert->Group->Choose your date field->The section will be printed for each Month) and then Brand fields, or you might use a Running Total, or you might use the method described by dgillz, but since you didn't share how the report should look, it's impossible to know which is right for you.

Once you've grouped a report, you can right click any field and do summary operations that will appear in the group footer(s), and optionally, grand totals.

Running Totals are useful for conditionally doing sums using the evaluate use a formula option, in particular they're good for unusual formatting requirements.

dgillz solution is handy when you need a conditional sum outside the bounds of grouping.

The final option might be a crosstab, in which you place your dates, change the group option to month, and then just place you field to sum.

I'd guess that the first solution makes the most sense for you.

Hope this helps to describe the many options, and in the future, show what you want too.

-k

 

[theamis]

Thank you so much for the replies!
It works the way I want it to be.

Thank you again.

 

[globularbob]

Hi

Great tips.... but when I try to sum the formula: If {DateField} in Date(2003,4,1) to date(2003,4,30) then {AmountField} else 0 then I get an error.

In my case, the formula looks like this:

sum(If {WeekNum}={@MaxWeek-1} then {TotalHrs} else 0)


It tells me that my sum needs needs a field. What am I not doing right?

Thanks!!!

 

[lbass]

Without more information, I'm guessing that "WeekNum" is a formula based on a datediff function that counts weeks from the beginning of the year, and that {@max week-1) is a maximum of {@weeknum} within a group -1 , with the aim of adding only those amounts in the next to the last week in the group.

Because the formula contains summaries already (maximum within a group), you can't insert a sum on it or write a formula like: sum({@formula},{yourgroup})

You would need to use a running total using the running total editor: Select {table.hours}, sum, evaluate based on a formula:

{@weeknumber} = maximum ({@weeknumber}, {yourgroup}) - 1

Reset on change of group. If you have no groups, eliminate the {yourgroup} part of the formula and reset never.

-LB

 

[globularbob]

Great tip! Thanks. Worked like a charm!!