Comparing values in a Crosstab 1

[69AceCool]

CRXI
Connecting to Remedy 5.1

Hi All,

is it possible to compare a value in one column to a value in a previous column?

Crosstab example:
25/02/06 04/03/06 11/03/06 Total
Email 118 123 178 419

Comparison I would like to do is 178/123-1=45%
If there is an increase of more than 20% I would like to highlight that particular 'cell' with a different colour. (There will be up to 20 different categories in the crosstab)

Not sure where to start on this one...?

Cheers!

 

[lbass]

Can you clarify whether you are only interested in comparing the most recent week with the previous week or whether you want to compare each week with the previous week.

Also, by "category" do you mean rows like "email" or columns (weeks)?

It would help to know what your summary and summary field are, along with the names of the rows and columns.

-LB

 

[69AceCool]

Apologies, compare each week with the previous week.

Category as in rows. (The Category in this instance is a part of the standard Remedy classification 'Category' 'Type' 'Item', e.g Email-Outlook-Error Message)

The summary is a count of the Case ID of the incident logged. (Hope that makes sense)

Cheers!

 

[lbass]

How many weeks will there be for the column field, just 3?

-LB

 

[69AceCool]

A maximum of 6 weeks rolling

 

[lbass]

I think the easiest solution would be a manual crosstab. Insert a group on {table.category} and then create a series of formulas like:

//{@currwk}:
if {table.date} in currentdate-6 to currentdate then 1

//{@prevwk}:
if {table.date} in currentdate-13 to currentdate-7 then 1

//etc. for six formulas total

Then insert a sum on each formula, drag the groupname into the group footer, and suppress the detail section. Then create your percentage formulas like the following:

sum({@currwk},{table.category}) % sum({@prevwk},{table.category}) - 100

Then select each percentage formula->format field->border->color->background->x+2 and enter:

if currentfieldvalue > 20 then crYellow else crNoColor

-LB

 

[69AceCool]

Thanks lbass, I knew I could do it with a manual crosstab, was just wondering if there was a way to do it with a 'normal' crosstab looks neater! . No probs though, I'll still use your suggestion above! Thanks again for the help, much appreciated!