Cross Tab Totals

[CR10]

Cross Tab :

sub red total
a 200 50 250
b 300 100 400
c 400 200 600
d 500 300 800

total 1400 650 1950

values from red & sub are all group sum. now, instead of getting the rows total, i would like to have the difference of sub and red. which will look like below :

sub red diff
a 200 50 150
b 300 100 200
c 400 200 200
d 500 300 200

total 1400 650 750

would highly appreciate any help i can get. thanks.

 

[IanWaterman]

create a formula

@diff
sub - red

Add this formula to cross tab columns

Ian

 

[CR10]

thanks ian. but i forgot to say that the red & sub amounts are records inside 1 field "TXNS" which i took the group sum and are showing in the cross tab.

 

[IanWaterman]

Not sure what you mean, are red and sub formulae?

If so please show contents.

If they are formula should still be possible to build a formula as I have described

@diff
{@sub} - {@red}

Ian

 

[lbass]

If you remove the column field and instead use conditional formulas like the following for summaries:

//{@sub}:
if {table.txns} = "sub" then {table.amt}

//{@red}:
if {table.txns} = "red" then {table.amt}

...you can then use formatting formula areas to create variables to do the calculation as follows. Select {@sub}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar subamt := currentfieldvalue;
false

Then select {@red}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar redamt := currentfieldvalue;
false

Create a new formula {@0} to act as a holder for the difference and add it as your third summary:

whilereadingrecords;
0

Then select this formula in the both the inner cell and column total->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar subamt;
numbervar redamt;
totext(subamt-redamt,0)

-LB

 

[CR10]

Hi guys!

thanks for the replies. however, i just can't seem to let it work. the sub & red are sum totals of subgroups. i used txns field in as column header in order to segregate the sub & reds in two columns from a single field. after which, i would like to have the difference of the sub from red.

thanks loads for your help guys!

 

[lbass]

Did you try my method? If so, what problem did you run into. It should work.

-LB

 

[CR10]

thanks lbass & lb for your help. however, i found a way to solve it. i created a formula converting the redemption column into negative values and using it as summary values, thus the total resulted into difference. after which, i formatted the negative values to not show the negative sign.

yet, i have a new problem to solve. i have to sort the summary values as per group. any solution that you can impart to me? thanks loads!

 

[lbass]

Please clarify what your column field is in the crosstab expert and what your summary field(s) are as they appear in the crosstab expert.

-LB

 

[CR10]

hi lb!

i created a formula (@negate):

if {transaction.transact} = "red" then (-1*({transaction.amount})) else {transaction.amount}

and used @negate to column & summary data which showed the difference in the total column (which is the net sales). however, i would like to sort the total column in ascending order in order to show the highest net sales.

thanks loads!

 

[lbass]

You didn't answer my question. Please identify column, row, and summary fields as they appear in the crosstab expert.

-LB