Cross Tab calculation of net increase in revenue

[only412c]

I am new to Crosstabs. I've created a crosstab the show the revenue for 2005 and 2006. I want to create another column in the crosstab to calculate the percent increase in revenue for each of these two years. Can someone direct me as to how to go about doing this? Thanks, Juan.

 

[lbass]

With crosstabs, the version of CR is especially important--so what version are you using?

-LB

 

[only412c]

Version 9

 

[lbass]

If you have a datefield for your column (printed on change of year), remove it and instead create two formulas:

//{@lastyear}:
if year({table.date}) = year(currentdate)-1 then {table.revenue}

//{@thisyear}:
if year({table.date}) = year(currentdate) then {table.revenue}

Use these as your summary fields in the crosstab. Also create a formula {@0}:

whilereadingrecords;
0

Insert this as the third summary (below the others).This will act as a holder for the percent you are going to display. Then select the summary for {@lastyear} in an inner cell->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar lastyr := currentfieldvalue;//use numbervar if a number
false

Then select the summary of {@thisyear}->right click->format field->suppress->x+2 and enter:
whileprintingrecords;
currencyvar thisyr := currentfieldvalue;//use numbervar if a number
false

Then select the summary of {@0}->right click->format field->DISPLAY STRING and enter:
whileprintingrecords;
currencyvar thisyr;
currencyvar lastyr;
if lastyr > 0 then
totext((thisyr-lastyr)%lastyr)+"%" else "0%";

I'm not sure whether you have this option in 9.0, but go to the customize style tab within the crosstab expert->see if you have the option of checking "display summaries horizontally" and check that if you do and also check "show summary labels".

You could also create a manual crosstab just as easily, where you group on what would have been your row field within the crosstab, and then use the formulas for the years that I showed above. Place these in the detail section and right click on each and insert a sum and then suppress the detail section and drag your groupname into the group footer. Then you could write a formula for the percent like this:

if sum({@lastyear},{table.groupfield}) > 0 then
(sum({@thisyear},{table.groupfield})-sum({@lastyear},{table.groupfield})) % sum({@lastyear},{table.groupfield})

Place this in the group footer and then click on the % icon in the toolbar.

-LB