YTD of Two Years... CROSSTABS???

[ebstarasia]

So what I am trying to do is generate a report that displays the YTD total volume of shipments. What I want the report to look like is to have possibly a cross tab with the first column being the name/group of the employee, then the second column being the customers they deal with. The following columns will be the YTD for 2011 and 2010.

How do I:
1) Get two columns to display YTD information from two separate years?
2) Have a parameter that takes in the current YTD, use that information for the current YTD, then minus a year for the previous year's YTD?

 

[lbass]

Set up a record selection formula like this:

(
{table.date} in YearToDate or
{table.date} in LastYearYTD
)

Then add {table.date} as your column field in the crosstab->group options-> on change of: Year.

Add the employer and customer fields as your row fields. Add the amount or quantity or whatever you are summarizing as your summary field.

-LB

 

[ebstarasia]

Thanks lbass,

I did that and it works. After going over the information with my boss, apparently his idea was a bit different than what I asked for. What he would like for the report to do is comparison of two columns of data: one from a previous year (mostly last year) and one from the year prior (mostly current), HOWEVER he wants to be able to set the date range so that if he wanted to compare the YTD or values between a particular date, he wants to be able to prompt/select expert for a specific date range.

At the moment yes the code you supplied does the YTD from the beginning of the year to current date (at the moment 6/13/2011) but my boss would like it more so to go from first of the year to 5/31/2011.

Any ideas?

 

[ebstarasia]

**just in case my explanation was confusing**

the idea is to enter a date range and have the report generate numbers from that date range and the previous year of that date range.

 

[lbass]

It would be simpler if you set up one date parameter {?EndDate} and then use a record selection formula like this:

(
{table.date} in date(year({?EndDate}),1,1) to {?EndDate} or
{table.date} in date(year({?EndDate})-1,1,1) to date(year({?EndDate})-1,month(?EndDate},day({?EndDate})
)

-LB

 

[fishymetrics]

I had a similar report request. I took an existing report and added a ?YTD_Month number parameter with default values 1 thru 12.

Then add to record selection...

{table.date} = {?Date_Range} and
month(table.date}) <= {?YTD_Month}

the user selects the month number they want the data thru.
Default it to 12 for the regular full year report

Make crosstab with months across and years down.

you would need your ?Date_range to start with 1/1/yyyy

Works for any number of years

 

[fishymetrics]

For a CrossTab with months across and grouped employee data labeled by year down the left..
assuming you have a group on date by year

make a formula to put on left of crosstab

//@Year_Employee_Group
//this splits and labels the data by year and employeename
//add as many years as necessary

If GroupName ({table.date}, "annually") = "2008" then
({table.Employeename} + " 2008")else
If GroupName (table.date}, "annually") = "2007" then ({table.employeename}} + " 2007")else
({table.employeename}} + "other year")

make a formula for month names to put across the top of crosstab

//@Group_Month
MonthName(month({ih_hist.ih_inv_date}), true)


looks something like this. shows only up to month chosen in ?YTD_Month

_____________________Jan Feb Mar Apr May Total

employeename1 2008
employyename2 2008
employeename1 2007
employyename2 2007