CROSSTAB CALCULATIONS 1

[psimon1]

Hello

If I pull from the following table:

employee, check date, amount
123,1/1/07, 1000
123,1/2/07, 1150

etc

into a crosstab:

CHECK DATE
1/1/07 1/2/07
123 1000 1150

is there a way in CRXI to input a calculated field, such that I get a separate (filterable) column for $150?

Thanks

 

[lbass]

Not sure what you mean by "filterable" or whether we are looking at Jan1 and Jan2 or Jan and Feb. If you set up the crosstab so that there is no column field, but with conditional formulas for summaries, like:

//{@Jan}:
if month({table.date}) = 1 and
year({table.date}) = 2007 then {table.amt}

//{@Feb}:
if month({table.date}) = 2 and
year({table.date}) = 2007 then {table.amt}

...you can create running totals in the formatting areas of these summaries so that you can do a calculation. First create a holder formula {@0} and make this your third crosstab summary:

whilereadingrecords;
0

Then in preview mode, select {@Jan}->format field->suppress->x+2 and enter:
whileprintingrecords;
numbervar jan := currentfieldvalue;
false

Do the same for {@Feb}:
whileprintingrecords;
numbervar feb := currentfieldvalue;
false

Then select {@0} and go to format field->common tab->DISPLAY STRING->x+2 and enter:
whileprintingrecords;
numbervar jan;
numbervar feb;
totext(feb-jan,2)

Go to the customize style tab of the crosstab->summary fields->horizontal->show labels.

PS. You are in the wrong forum. Try forum767 or forum149 for future posts, as appropriate.

-LB