Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cross-Tab Help

Status
Not open for further replies.

grecon

Technical User
Mar 8, 2007
78
US
Hi I have a cross tab that has the months (@monthnames) of the year as the row and the years(ORDER_DT) as the columns for sales figures (SUM of ORDER_TOT), now I want to show the difference for each month/year - for example:
2011 2012 Difference
January 50000 35000 -15000
Feb 75000 85000 10000
March 100000 165000 65000

I can't figure out what formula to use to get the difference between the two??? How do I get it to show the difference???? Thanks
 
I don't think you can. Accumulate the totals separately, using a Mock-Crosstab. (Use SEARCh for details.)

[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]
 
You can do this, but it would be just as easy to create a manual crosstab in this case. You would group on monthname and then add conditional formulas, one for each year:

//{@lastyear} for the detail section:
if year({table.date})=year(currentdate)-1 then
{table.amt}

//{@thisyear} for the detail section:
if year({table.date})=year(currentdate) then
{table.amt}

Insert sums on these at the group footer and then create another formula for the group footer:

//{@diff}:
sum({@thisyear},{@monthname})-sum({@lastyear},{@monthname})

Drag the groupname into the group footer and suppress the details and group header.

-LB
 
It depends on what crystal version you have.

With Crystal 2008/11 you can use an embedded summary to pick up the summary in this cell and then one in the previous row. and then subtract them.

Examples of how to use this is in the training database and report files on my web site. That's a free download with som

Bruce Ferguson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top