Calculation of Today, Month and YTD Sales Transacitons

[BHAT100]

I have a sales report, I need to calculate Today sales, month sales and YTD sales.

I am using following formula to calculate Today and YTD figures but I am not able figure out how to calculate month's figures.

I am using {?Sale -End Date} parameter

Any help in month's formula would be great help

if {SOP30200.GLPOSTDT} > {?Sale -End Date}-1 and {SOP30200.GLPOSTDT} <= {?Sale -End Date}
and month({?Sale -End Date}) = month ({?Sale - Start Date })
and {SOP30200.SOPTYPE} = 3
then {SOP30300.XTNDPRCE}
else if {SOP30200.GLPOSTDT} > {?Sale -End Date}-1 and {SOP30200.GLPOSTDT} <= {?Sale -End Date}
and month({?Sale -End Date}) = month ({?Sale - Start Date })
and {SOP30200.SOPTYPE} = 4
then {SOP30300.XTNDPRCE}*-1
else 0.00

and for YTD figures:

if {SOP30200.GLPOSTDT} >= CDateTime(2005,01,01,00,00,00) and {SOP30200.GLPOSTDT} <= {?Sale -End Date}
and {SOP30200.SOPTYPE} = 3
then {SOP30300.EXTDCOST}
else if {SOP30200.GLPOSTDT} >= CDateTime(2005,01,01,00,00,00) and {SOP30200.GLPOSTDT} <= {?Sale -End Date}
and {SOP30200.SOPTYPE} = 4
then {SOP30300.EXTDCOST}*-1
else 0.00

Thanks

-b

 

[synapsevampire]

Knowing where you need to display this is critical.

You can create groups and use Running Totals with Evaluate->Use a Formula to do all of this if displaying at the group level is OK.

Also knowing if this means a month to date, or the last full month is important to distinguish.

Rather than showing formulas for something that you already have, you might want to post some technical information:

Crystal version
Dtabase/connectivity used
Example data
Expected output (where and the layout)

-k

 

[MrBillSC]

I would set your record selection formula to select records if year of GLPostDt = year of ?Sales_End_Date and GLPostDt <= ?Sales_End_Date.

Then I would creat group 1 on month of GLPostDt and group 2 on GLPostDt.

Subtotal you data fields in both groups and report total.

Suppress group footer 2 if GLPostDt <> ?Sales_End_Date. This will by your Today's Sales Total.

Suppress group footer 1 if month of GLPostDt <> Month of ?Sales_End_Date. This will be you month-to-date totals.

Your report footer will be your YTD totals.

MrBill

 

[BHAT100]

I would like display in three separate column on detail column.

My report is as below:

Group1: Location
Group2: Salesperson
Group3: Customer
Group4: Item
Details: where I am placing my above formula for Today, YTD and I want to place month formula also on detail column
Group footer4: item
Group footer3: Customer
Group footer2: Salesperson
Group footer1: Cust Class

and user would be entering ?end date parameter

Thanks

-b

 

[BHAT100]

month means month to date, not last full month

Thanks

-b

 

[BHAT100]

crystal report 9.0
SQL SERVER 2000
database: great plains

 

[lbass]

For the month formula, use dates the following way:

if {SOP30200.GLPOSTDT} > {?SaleEndDate}-day({?SaleEndDate}) and
{SOP30200.GLPOSTDT} < {?SaleEndDate} + 1 then//etc.

-LB

 

[BHAT100]

Hi Lbass

Thanks for your reply but this formula is giving error at then saying "the ) is missing.

if {SOP30200.GLPOSTDT} > {?Sale -End Date}
-day({?Sale -End Date}
and {SOP30200.GLPOSTDT} < {?Sale -End Date}+1
and {SOP30200.SOPTYPE} = 3
then {SOP30300.XTNDPRCE}
else if {SOP30200.GLPOSTDT} > {?Sale -End Date}-
day({?Sale -End Date}
and {SOP30200.GLPOSTDT} < {?Sale -End Date}+1
and {SOP30200.SOPTYPE} = 4
then {SOP30300.XTNDPRCE}* -1
else 0

Thanks

-b

 

[synapsevampire]

Try:

if {SOP30200.GLPOSTDT} > {?Sale -End Date}
-day({?Sale -End Date})
and {SOP30200.GLPOSTDT} < {?Sale -End Date}+1
and {SOP30200.SOPTYPE} = 3
then {SOP30300.XTNDPRCE}
else if {SOP30200.GLPOSTDT} > {?Sale -End Date}-
day({?Sale -End Date})
and {SOP30200.GLPOSTDT} < {?Sale -End Date}+1
and {SOP30200.SOPTYPE} = 4
then {SOP30300.XTNDPRCE}* -1
else 0

-k

 

[BHAT100]

Thanks, it worked

have a great weekend

Thanks

-b