comparing sales for current month and last year month 1

[eoggb]

I am try to find sales for a selected month based on a (Date pram) for the current year and for the same month for last year...problem is I return the sales for the same month (good) but don't know how to have it call to last year and current year...not sure if the script is correct. any suggestions

//----------lastyear---------------

IF {?DateRangeType} = "Monthly" then
if date(dateadd("m", 0, dateadd("d", -365, {?Date }))) = ({DailySales_Summary_vw.SalesDate})then
if ({MMS_Store_Region_vw.Region}="USA")then
{DailySales_Summary_vw.Sales}* {?ExchRate}
else
{DailySales_Summary_vw.Sales}

//-----------currentyear------------

IF {?DateRangeType} = "Monthly" then
if date(dateadd("m", 0, {?Date })) = ({DailySales_Summary_vw.SalesDate})then
if ({MMS_Store_Region_vw.Region}="USA")then
{DailySales_Summary_vw.Sales}* {?ExchRate}
else
{DailySales_Summary_vw.Sales}

 

[lbass]

//----------lastyear---------------

IF {?DateRangeType} = "Monthly" then
if {DailySales_Summary_vw.SalesDate} in dateserial(year({?date})-1, month({?date}), 01) to
dateserial(year({?date})-1, month({?date})+1, 01)-1
then
if ({MMS_Store_Region_vw.Region}="USA")then
{DailySales_Summary_vw.Sales}* {?ExchRate}
else
{DailySales_Summary_vw.Sales}

//-----------currentyear------------

IF {?DateRangeType} = "Monthly" then
if {DailySales_Summary_vw.SalesDate} in dateserial(year({?date}), month({?date}), 01) to
dateserial(year({?date}), month({?date})+1, 01)-1
then
if ({MMS_Store_Region_vw.Region}="USA")then
{DailySales_Summary_vw.Sales}* {?ExchRate}
else
{DailySales_Summary_vw.Sales}

-LB

 

[synapsevampire]

I may misunderstand, but if you're wanting just the month and last years month, use the Report->Select Formula->Record and place:

If {?DateRangeType} = "Monthly" then
(
{DailySales_Summary_vw.SalesDate} = dateserial(year({?date})-1, month({?date}), 01) to
dateserial(year({?date})-1, month({?date})+1, 01)-1
or
{DailySales_Summary_vw.SalesDate} = dateserial(year({?date}), month({?date}), 01) to
dateserial(year({?date}), month({?date})+1, 01)-1
)

This will limit what's returned by the database.

Now create formulas for each month you want to display, as in:

if ({MMS_Store_Region_vw.Region}="USA")
and year({DailySales_Summary_vw.SalesDate}) < year({?DateRangeType}) then
{DailySales_Summary_vw.Sales}* {?ExchRate}
else
if year({DailySales_Summary_vw.SalesDate}) < year({?DateRangeType})
{DailySales_Summary_vw.Sales}

The idea being to not return all data and then do the formulas, eliminate them from the data returned first.

-k