Sum Current Month Forecast?

[crystalhelp1]

I am trying to create a SQL Expression in Crystal to sum by part number all forecast in current month. See data below:

Part Number Qty Date
P4276 100 01/01/2012
P4276 200 02/02/2012
P4276 150 06/13/2012
P4276 200 06/15/2012
P4276 50 06/24/2012
P4276 100 06/28/2012
P4276 500 07/05/2012

I just want to sum the quantities in June and ignore the quantities in previous and future months (e.g. 01/01/2012, 02/02/2012, and 07/05/2012).

 

[kray4660]

It really depends on the Data Source (Oracle, MS-SQL, Informix). If you used a select expert formula, it would be easier to set up, but it could be slower.

I hope this helps.

 

[briangriffin]

Put this formula in the detail section:

whileprintingrecords;
numbervar v_forecast;

if month(yourtable.datefield) = month(currentdate)
then v_forecase := v_forecast + yourtable.qty)
else
v_forecast;


If you're going to be grouping on partnumber and putting the sum in the group footer, then you'll need to put this in the group header:

whileprintingrecords;
numbervar v_forecast := 0;


And to display the sum put this in the group footer:

whileprintingrecords;
numbervar v_forecast;
v_forecast

You can suppress the formula in the detail section if you don't need to see the running total.


If what you really want to do is to only include the current month's records in the report and not see the other months, then instead put this in your record selection formula:

month(yourtable.datefield) = month(currentdate)

 

[briangriffin]

You'll actually have to compare the year also, if your records will ever span more than a 12 month period - better safe than sorry.

whileprintingrecords;
numbervar v_forecast;

if month(yourtable.datefield) = month(currentdate)
and year(yourtable.datefield) = year(currentdate)
then v_forecast := v_forecast + yourtable.qty)
else
v_forecast;




And if you go the select formula route, you'll also have to compare years:

month(yourtable.datefield) = month(currentdate)
and year(yourtable.datefield) = year(currentdate)

 

[lbass]

Create a formula like this:

if month({table.date})=month(currentdate) and
year({table.date})=year(currentdate) then
{table.amount}

Place this is in the detail section and right click on it and insert a sum.

-LB