Current Year and Past Year calculation

[Nro]

Hi all. I'm looking to calculate the previous year result for sales, based to a date criteria .

For example, my user enter a date period (Ex. 01-01-2005 to 31-01-2005). I use this criteria to filter the results from an order table (think NorthWind Orders). I group by customer no, and get the sales for jan. 2005 on one column. On the other column (on the same line), I want the result for the previous year (01-01-2004 to 31-01-2004)

Ex.
Results for 01-01-2005 to 31-01-2005
Customer : BARRTOK
Sales Current Previous Year
2,000.00$ 1,566.77$


I'm new in Crystal SQL programming and I don't know how to achieve this

I don't want to use a cross-tab because the user wants to calculate a performance ratio.

I'm using Crystal 10 with SQL Server 2000

Thanks in advance.

Nro

 

[lbass]

You could create two parameters {?start} and {?end} and use a record selection formula like:

{table.date} in {?start} to {?end} or
{table.date} in dateadd("yyyy", -1, {?start}) to
dateadd("yyyy",-1,{?end})

Then you can create a manual crosstab by inserting a group on customer and then creating formulas like:

//{@2005}:
if {table.date} in {?start} to {?end} then {table.sales}

//{@2004}:
if {table.date} in dateadd("yyyy", -1, {?start}) to
dateadd("yyyy",-1,{?end}) then {table.sales}

Then place these formulas in the detail section and right click on each and insert a sum at the customer group level. Then suppress the detail section.

You might still be able to use an inserted crosstab despite your need for a performance ratio. Please explain what the ratio is and how you would want it displayed.

-LB

 

[Nro]

Thanks LB.

The perforance Ratio is only the comparaison between current and past (we have an increase of 27.65% in current year)

Ex :
Current Previous Year Aug./Loss
2,000.00$ 1,566.77$ 27.65%

I don't think it's possible to calculate this with a crossTab.

Any idea?

Nro

 

[lbass]

Ken Hamady just revised a solution for this in one of his newsletters:

http://www.kenhamady.com/news0407.html

See "Percentages in a Crosstab", Technique #2 for this exact issue. You would need to set up the crosstab without using year as the column, and instead enter the formulas from my previous post as the summaries. Then you would add a third formula:

//{@percent change}:
whilereadingrecords;
0

You would go to the customize style tab and select "Horizontal" and "Show Labels" Under "Summaries". Then follow his suggestion for creating variables in the field format-> suppression areas for each of the first two formulas, and use the display string area on the field format->common screen to create the percent change formula. I think the display string formula option became available in 9.0 and higher.

-LB

 

[Nro]

Thanks for all your support.

I'll take a lot of ideas from your two reply. The next step for me is to test the crosstab with differrent data at my customer place. I'll give you some feedback soon.

Nro