showing the difference between 2 columns in a crosstab?

[AnnetteB1305]

Is there a way of showing the difference between 2 columns in a crosstab. I have 2 columns that show the number of calls for various departments in the month selected and previous month and want a column for the difference between the 2 months. Is this possible?

 

[lbass]

You will have to be specific about the crosstab setup, including showing formulas you are using, etc.

-LB

 

[AnnetteB1305]

The values in the crosstab are a distinct count of a call id and the rows are a dept id field from the database and the columns are months. I then need to show the difference between month 1 count of call id and month 2 count of call id

 

[lbass]

Instead of using month as the column field, you should create conditional formulas for summary fields, one for each month, like this:

//{@prevmo}:
if {table.date} in dateserial(year({?Date}),month({?Date})-1,1) to
dateserial(year({?Date}),month({?Date},1)-1 then
{table.callID} else
tonumber({@null})

//{@currmo}:
if {table.date} in dateserial(year({?Date}),month({?Date}),1) to
dateserial(year({?Date}),month({?Date})+1,1)-1 then
{table.callID} else
tonumber({@null})

...where {@null} is a new formula you open and save without entering anything. Then to get the difference right click on the {@prevmo} summary->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar prev := currentfieldvalue;
false

Then select the {@currmo} summary->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar curr := currentfieldvalue;
false

Then create a formula {@0} like this:

whilereadingrecords;
0

Add this as your third summary and then right click on it->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar prev;
numbervar curr;
totext(curr-prev),0,"")

Go to the customize style tab and select "horizontal" and "show labels" for the summary fields. Then edit the labels as necessary.

-LB

 

[AnnetteB1305]

I had to change the @prevmo formula as it errored but do you mean put these formulas as the column headers or are you saying use these as the summarized fields, can you please clarify where to use these formulas

Thanks in advance

 

[AnnetteB1305]

If you did mean use this as the crosstab column headers I am then getting a column for each callid so this does not appear to work

 

[lbass]

No, these are the new summary fields, not a column field. You would not use any column field. The formula is missing a paren. Should have been:

//{@prevmo}:
if {table.date} in dateserial(year({?Date}),month({?Date})-1,1) to
dateserial(year({?Date}),month({?Date}[red])[/red],1)-1 then
{table.callID} else
tonumber({@null})

Also, the display string formula should be:

whileprintingrecords;
numbervar prev;
numbervar curr;
totext(curr-prev,0,"")//removed an extra paren

-LB

 

[AnnetteB1305]

My fault but I also forgot to mention that on one of the crosstabs that I need to show this difference I also was showing the top ten based on distinct count of call id but when I do your solutions the top 10 no longer works so I don't know if the solution is working as there are thousands of entries which I don't need! Is there a way of just showing the top 10 and then I think that I have this and can roll it out to all the other crosstabs where this is required.

Thanks in advance and apologies

Annette

 

[lbass]

top10 of what?

-LB

 

[lbass]

Okay, add a third summary field:

//{@Total}:
if {table.date} in dateserial(year({?Date}),month({?Date})-1,1) to
dateserial(year({?Date}),month({?Date})+1,1)-1 then
{table.callID} else
tonumber({@null})

Then do the top10 on this summary.

-LB

 

[AnnetteB1305]

This isn't working as the topn will only allow me to base it on the distinct count of {table.callref}, nothing else is in the dropdown

 

[lbass]

Please take another look at the dropdown after adding the {@Total} summary to the crosstab. When you first look at the group sort, you will see any existing group sorts. You need to reopen the dropdown to see new options.

-LB