×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Cross tab advanced calculations
2

Cross tab advanced calculations

Cross tab advanced calculations

(OP)
Is there a way to add a column to a cross tab to create a calculation of the total and an inputed formula?

Cross tab the budget is a inputted amount based on sales for Jan, Feb, Mar, Apr
div1 sales div2 sales div3 sales total New calculated column
Jan 500 200 100 800 Formula of budgeted sales (1000) less total of 800 so display 200
Feb 200 100 50 350 budget 500 less 350 so display 150
Mar 300 200 100 600 budget 700 less 600 so display 100
Apr 700 100 100 900 budget 800 less 900 so display (100)


RE: Cross tab advanced calculations

(OP)
Column1 column2 column3 column4 Column5 Total Budget Total
July
$9,017,580 $17,659,983 $4,868,445 $4,215,323 $4,492,042 $40,253,373 This field equals an inputted budget # for July less the Total for July
August
$5,823,131 $26,925,942 $3,983,186 $7,820,524 $5,088,941 $49,641,724
September
$5,830,349 $21,485,297 $3,805,019 $6,420,305 $4,682,246 $42,223,216
October
$4,451,164 $17,464,080 $5,599,409 $6,029,141 $5,687,803 $39,231,597
November
$4,041,249 $16,581,585 $3,847,243 $4,717,000 $4,341,604 $33,528,681
December
$3,741,116 $10,657,690 $2,441,764 $3,751,979 $3,216,431 $23,808,980
January
$5,102,203 $12,867,593 $3,350,294 $2,498,894 $3,179,772 $26,998,756
February
$5,672,371 $16,698,689 $4,051,667 $5,516,871 $4,027,190 $35,966,788
March
$7,916,508 $25,187,858 $5,221,223 $7,360,574 $5,747,146 $51,433,309
April
$7,930,279 $22,706,092 $8,240,979 $8,539,161 $5,679,809 $53,096,320
May
$14,483,810 $33,678,359 $8,169,225 $10,083,476 $6,452,539 $72,867,409
3

RE: Cross tab advanced calculations

You can add the total field a second time and then go to the crosstab expert customize style tab and select "horizontal" summaries and "show labels". In preview mode, select the (new) inner column and column label and suppress them and then minimize the width by grabbing and decreasing the margins. You will then see the row total twice at the end of the row. Edit the text of the label for the new column to say Budget Less Total. Then select the total->right click->format field->display string->x+2 and enter:

whileprintingrecords;
currencyvar budg := 0;

budg := (
select month(gridrowcolumnvalue("table.date")) //replace "table.date" with your actual date field, replacing the brackets with double quotes as shown
case 1 : 100000 //enter the actual budget figures here for each month e.g., 250,000 for 100,000, etc.
case 2 : 200000
case 3 : 300000
case 4 : 400000
case 5 : 500000
case 6 : 600000
case 7 : 700000
case 8 : 800000
case 9 : 900000
case 10 : 1000000
case 11 : 1100000
case 12 : 1200000
);
totext((budg-currentfieldvalue),0);

Newer versions of CR have an ability to add calculated fields in some way, but I have not learned the method. I think Ken Hamady has a paper on the newer crosstab functionality if you want to look into it.

-LB

RE: Cross tab advanced calculations

Like LB I am old school and have not used calculated members very often.
I have booked marked this youtube explanation as it is a good guide to basics and will get you started

https://www.youtube.com/watch?v=RpAEJqFe4IY
Ian

RE: Cross tab advanced calculations

(OP)
LB - This worked perfectly and thank you so much for always being so knowledgeable and helpful!

One thing is that my vertical grand total for the new column is still adding up to the same as the original column. Do you have another formula that will correct this?

RE: Cross tab advanced calculations

Change the existing row-level display formula to the following by adding the two lines shown:

whileprintingrecords;
currencyvar budg := 0;
currencyvar sumbudg; //add this line

budg := (
select month(gridrowcolumnvalue("table.date")) //replace "table.date" with your actual date field, replacing the brackets with double quotes as shown
case 1 : 100000 //enter the actual budget figures here for each month e.g., 250,000 for 100,000, etc.
case 2 : 200000
case 3 : 300000
case 4 : 400000
case 5 : 500000
case 6 : 600000
case 7 : 700000
case 8 : 800000
case 9 : 900000
case 10 : 1000000
case 11 : 1100000
case 12 : 1200000
);
sumbudg := sumbudg + budg; //add this line
totext((budg-currentfieldvalue),0);

Then select the grand total of the calculation column->right click->format field->display string->x+2 and enter:

whileprintingrecords;
currencyvar sumbudg;
totext(sumbudg-currentfieldvalue,0);

-LB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close