Getting Last years MTD and This Years MTD

[jspur2003]

I need some help I need help getting Last years Month to date and This years Month to date. And for it to print a sales total of items sold for a certain Item EX below

ITEM; LastYR MTD QTY sold; ThisYr MTD Qty sold; Difference
Bikes 10 15 +5

Our database is set up where date is its own field and QTY invoiced or sold is its own field and Item is its own field.. I know how to make the differences and i can create a standard report where it will list the Item, Date, Qty Sold, however it doesnt put them side by side it makes it so i have last years MTD with whats sold then in the list below is this years MTD... Also the Date field is a paramater field as well so we can enter for differents months instead of previous month.. Any help would be greatly appreciated..

 

[synapsevampire]

First set the Report->Edit Selection Formula to something like:

month({table.date}) <= month(currentdate)
and
(
year({table.date}) >= year(currentdate)
and
year({table.date}) = year(currentdate)
)

Group the report by Item

Use Running Totals and select reset at the item, and in the evaluate use a formula use
year({table.date}) = year(currentdate)-1

and for the other use

year({table.date}) = year(currentdate)

You can reference these RTs in another formula to show the difference (they'll show up in the formula editor).

-k

 

[vidru]

If your Date parameter represents the 'ToDate' part of MonthToDate, then I'd start with this for your record selection formula, so from the start, you're only dealing with the dates you actually need. It should pass through to the database:
[tt]
{Table.DateField} in [Date(Year({?Date}), Month({?Date}), 1) to {?Date}]
or
{Table.DateField} in [Date(Year({?Date}) - 1, Month({?Date}), 1) to Date(Year({?Date}) - 1, Month({?Date}), Day({?Date}))]
[/tt]
Group the report by Item, and create these two formulas:
[tt]//@PrevMTD
If Year({Table.DateField}) = Year({?Date}) - 1 then
{Table.QtyField}
Else
0

//@CurrMTD
If Year({Table.DateField}) = Year({?Date}) then
{Table.QtyField}
Else
0
[/tt]
Place the above formulas into your Details section, then insert group subtotals for both formulas. Then, create your 'Difference' formula:
[tt]
//@Difference
//Put this in your Item group footer after your subtotals
Sum ({@CurrMTD}, {Table.ItemField}) - Sum ({@PrevMTD}, {TT_Table.ItemField})
[/tt]
Suppress the Details section, and you should be golden, like Pony Boy in the Outsiders (man, wasn't C. Thomas Howell great in that, Swayze as the older brother, and don't forget Leif Garrett...).

-dave

 

[synapsevampire]

Sorry, screwed that up:

month({table.date}) <= month(currentdate)
and
(
year({table.date}) >= year(currentdate)-1
and
year({table.date}) = year(currentdate)
)