THIS YEAR'S UNIT SOLD VS LAST YEAR'S UNITS SOLD 1

[grcrystal]

I am using Sage 50 (Peachtree) Quantum 2013, Crystal Reports 2008 and PSQL is the database engine.

I am designing a report to show Units Sold for Inventory Items for a specific date range and Units sold for Inventory Items Last Year (which is specific date range less one year).

I am getting an error in my formula for Last Year's Units Sold:

IF {JrnlRow.RowDate} >= dateSERIAL(YEAR({@BEG DATE})-1, MONTH({@BEG DATE}),DAY({@BEG DATE}))
OR IF {JrnlRow.RowDate} <= dateSERIAL(YEAR({@END DATE})-1, MONTH({@END DATE}),DAY({@END DATE}))
then {@Units Sold In Liters}

The error is: the keyword "then" is missing

Can someone tell me what is wrong with the formula? Am I missing a parenthesis somewhere?
I want to show the Item ID, Item Description, Units Sold, Units Sold in Liters for two time periods.
The first time period is the date range entered by the user ({?Date Range}) and the second time period is for a year ago from date range entered by the user.
For example:
UNITS SOLD JAN 1, 2012 through JUN 30, 2012 vs UNITS SOLD JAN 1, 2011 through JUN 30, 2011
ITEM ID ITEM DESCRIPTION UNITS SOLD LITERS SOLD ITEM ID ITEM DESCRIPTION UNITS SOLD LITERS SOLD
A OIL 10 100 A OIL 5 50
B GREASE 1 1 B GREASE 0 0
C ULTRA SYN 14 265 C ULTRA SYN 18 341

I have a parameter for the report date range {?Date Range} where the user enters a starting date and a ending date.
My record selection is:
{JrnlRow.RowDate} = {?Date Range} or {JrnlRow.RowDate} >= DATESERIAL(YEAR({@BEG DATE})-1,MONTH({@BEG DATE}),DAY({@END DATE}))
AND {JrnlRow.RowDate} >= DATESERIAL(YEAR({@END DATE})-1,MONTH({@END DATE}),DAY({@END DATE}))

{@BEG DATE} = Minimum({?Date Range})
{@END DATE} = Maximum({?Date Range})

Thank you for your help.

 

[lbass]

{JrnlRow.RowDate} = {?Date Range} or
(//add this
{JrnlRow.RowDate} >= DATESERIAL(YEAR({@BEG DATE})-1,MONTH({@BEG DATE}),DAY({@END DATE})) AND
{JrnlRow.RowDate} <= //change the >= to <=
DATESERIAL(YEAR({@END DATE})-1,MONTH({@END DATE}),DAY({@END DATE}))
) //add this

Then change your formula to (replace "or" with "and" and remove the second "if"):

IF {JrnlRow.RowDate} >= dateSERIAL(YEAR({@BEG DATE})-1, MONTH({@BEG DATE}),DAY({@BEG DATE})) and
{JrnlRow.RowDate} <= dateSERIAL(YEAR({@END DATE})-1, MONTH({@END DATE}),DAY({@END DATE})) then
{@Units Sold In Liters}

-LB

 

[grcrystal]

Thank you LB! I made the changes and now there are no errors.
Thank you so much!

 

[grcrystal]

Don't know if I should start a new post, or just post the additional question here, since it is the same report.

My report is not pulling the transactions for Last Year.

My selection formula:
{JrnlRow.Journal} = 3 and //this pulls sales transactions
{LineItem.ItemID} <> "M10920" and
{LineItem.ItemID} in ["M10055" to "M999800"] and
{JrnlRow.RowType} = 0 and //this pulls a normal transaction once - without it the transactions triple
{JrnlRow.RowDate} = {?Date Range} or ({JrnlRow.RowDate} >= DATESERIAL(YEAR({@BEG DATE})-1,MONTH({@BEG DATE}),DAY({@END DATE}))
AND {JrnlRow.RowDate} <= DATESERIAL(YEAR({@END DATE})-1,MONTH({@END DATE}),DAY({@END DATE})))

I want the data to pull this years quantity sold and last years quantity sold for the same dates (different years).
(For example, if the user enters 1/1/12 to 6/3012, I need to the report to show the quantity sold for this year 1/1/12 to 6/30/12 and the quantity sold for last year 1/1/11 to 6/30/11.

The report is showing transactions for all inventory items (not just those in M10055 to M999800) and last years units sold all appear as 0.

I have 2 formulas for the units sold:
{@UNITS SOLD THIS YEAR} = IF {JrnlRow.RowDate} IN {?Date Range} THEN {JrnlRow.Quantity}

{@UNITS SOLD LAST YEAR} = IF {JrnlRow.RowDate} >= dateSERIAL(YEAR({@BEG DATE})-1, MONTH({@BEG DATE}),DAY({@BEG DATE}))
AND {JrnlRow.RowDate} <= dateSERIAL(YEAR({@END DATE})-1, MONTH({@END DATE}),DAY({@END DATE}))
then {JrnlRow.Quantity}

I did check to see that there were units sold 1/1/11 to 6/30/11.
For example, for 1/1/11 to 6/30/11 and item M12901 the total units sold was 7; for 1/1/12 to 6/30/12 the total units sold was 14.
The crystal report is showing 0 units sold for 1/1/11 to 6/30/11.

 

[grcrystal]

So the prior post should have included two questions:

1) How do I get the report to only include the items I've asked for in my selection ({LineItem.ItemID} <> "M10920" and {LineItem.ItemID} in ["M10055" to "M999800"])?

2) How do I get the correct formula so the report displays the units sold for Last Year?
Current formula:
{@UNITS SOLD LAST YEAR} = IF {JrnlRow.RowDate} >= dateSERIAL(YEAR({@BEG DATE})-1, MONTH({@BEG DATE}),DAY({@BEG DATE}))
AND {JrnlRow.RowDate} <= dateSERIAL(YEAR({@END DATE})-1, MONTH({@END DATE}),DAY({@END DATE}))
then {JrnlRow.Quantity}

 

[pmax9999]

I think there are a couple of likely problems with your record selection. Try this:

{JrnlRow.Journal} = 3 and //this pulls sales transactions
{LineItem.ItemID} <> "M10920" and
{LineItem.ItemID} in ["M10055" to "M999800"] and
{JrnlRow.RowType} = 0 and //this pulls a normal transaction once - without it the transactions triple
(
	{JrnlRow.RowDate} = {?Date Range} or 
	(
		{JrnlRow.RowDate} >= DATESERIAL(YEAR({@BEG DATE})-1,MONTH({@BEG DATE}),DAY({@BEG DATE})) AND 
		{JrnlRow.RowDate} <= DATESERIAL(YEAR({@END DATE})-1,MONTH({@END DATE}),DAY({@END DATE}))
	)
)

You need to use brackets to force the correct order for interpretation when mixing and and or, the day component of the previous year staring date used {@END DATE} instead of {@BEG DATE}.

Once the report is returning the correct data, the formula for {@UNITS SOLD LAST YEAR} looks like it should work.

Hope this helps


Cheers
Pete

 

[lbass]

Instead of using Year({@begdate})-1 in the formula (and same for end), you should use the fiscal year. So if your fiscal year runs July 1 to June 30 use a formula like this:

//{@fiscal year}:
year(minimum({?daterange})+184)

...instead of beg date or end date.

{JrnlRow.RowDate} >= DATESERIAL({@fiscal year})-1,MONTH({@BEG DATE}),DAY({@BEG DATE})) AND
{JrnlRow.RowDate} <= DATESERIAL({@fiscal year}-1,MONTH({@END DATE}),DAY({@END DATE}))

-LB