Need an average column on a crosstab

[AnnetteB1305]

Hello,

Is there a way of having an average of 2 other columns in a crosstab. I have a crosstab which shows a count of calls handled by particular departments in a given month and the previous month and then the difference between these columns. I need to add a column which shows the average of month 1 and the previous month and then a further column which shows the difference from the @currmo and this average column i.e. @currmo-averagecolumn.

My crosstab is set up in a subreport and is set up as follows:-

Rows
@Not1&2nd
which is If not({@HDInvolved} in ["1STLINE","2NDLINE"]) then {@HDInvolved}

the @HDInvolved formula is left(table.groupid),7

Columns are

//@currmo
if {@Logdate} in dateserial(year({?Month}),month({?Month}),1) to
dateserial(year({?Month}),month({?Month})+1,1)-1 then
{opencall1.callref} else
tonumber({@null})

//@prevmo
if {@Logdate} in dateserial(year({?Month}),month({?Month})-1,1) to
dateserial(year({?Month}),month({?Month}),1)-1 then
{opencall1.callref} else
tonumber({@null})

//@0
whilereadingrecords;
0

Then I have suppression formulas on the @currmo summary and the @prevmo summary and a display string formula on the @0 summary.


Thanks for any help

Annette

 

[agorjest]

From my experience, the CR cross tab function is very basic and does not handle most complex summaries and calculations. For these situations, I've usually had to do "manual" cross tabs, where group sections function as the rows and conditional formulas function as the columns. It's more time consuming, but in many cases it's the only way to get the results you want.

 

[lbass]

Annette,

You can use the same method that I showed you for getting the difference between the two columns. I'm not sure why you are using an inserted crosstab in the first place--at least I don't remember. This would probably be simpler if you used a manual crosstab where you grouped on the row field and then did the necessary calcuations.

-LB

 

[AnnetteB1305]

LB

So would I then need a 4th formula say @1 which was again whilereadingrecords;
0

and then would the displaystring condition be

whileprintingrecords;
numbervar prev;
numbervar curr;
totext((curr+prev/2)),0,"")

or am I completely off the mark?

 

[AnnetteB1305]

It's ok I got it with


whileprintingrecords;
numbervar prev;
numbervar curr;
totext((curr+prev/2),0)

Thanks for your help

Annette

 

[AnnetteB1305]

However trying to do the difference between this average column and the prevmo figure and can't get this to work.

I have done a suppression formula on the avg column (@1) which is as follows:-

whileprintingrecords;
numbervar avg := currentfieldvalue;
false

Then I have created a new formula @2 and then entered a displaystring condition which is:-

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

But this is not working the figure is not as it should be am I completely off the mark with this?

 

[lbass]

Please note that these are not suppression formulas. You are just using the formatting formula areas to create running totals within the crosstab.

You don't need to add anything in the suppression area of your {@1} summary. The only value it has right now is 0, and so that is what the currentfieldvalue is. If you have added this as your fourth summary, you can then use a display string formula like this:

whileprintingrecords;
numbervar prev;
numbervar curr;
numbervar avg := (curr+prev)/2;
totext((prev-avg),0,"")

-LB

 

[AnnetteB1305]

I see that's worked however there's a issue if the avg is the same as the prev then it is showing 1 rather than 0

 

[AnnetteB1305]

Actually I'm wrong it works apart from the top line always 1 out???

 

[lbass]

I don't know what you are saying.

-LB

 

[AnnetteB1305]

So on the top line of one cross tab the prevmo number is 128 and the average is 128 so the difference should be 0 however it is 1. I have this in another crosstab and the top row the prevmo total is 144 and the 12mth avg is 128 and therefore the difference should be 16. All the other rows in this crosstab are fine and I have this in another crosstab and that is fine too!!! Will this be down be rounding issues as the avg is formatted at whole numbers only?

 

[lbass]

What IS the "top line"--is it a total or subtotal? Or is it the first detail row?

Please go into the display formulas for prev, curr, and avg and change them so that they show two decimals by changing the totext function to:

totext(<variablenamehere>,2)

Please show a mock sample in this thread of your results across a few rows so we can see if it is a rounding issue.

-LB

 

[AnnetteB1305]

Sorry I'm being a girl yes the top line is the first detail row and the average would be 127.5 if it wasn't whole numbers and the prevmo title is 128 so if rounding up it would be 1 rather than 0. So how can I overcome this?

 

[lbass]

I am a "girl" too--something wrong with that? If you don't want to use decimals, then you should change the prev and curr formulas (where they are set in the suppression area) to:

whileprintingrecords;
numbervar prev := round(currentfieldvalue);
false

Repeat for curr.

You should then also change the display formula to:

whileprintingrecords;
numbervar prev;
numbervar curr;
numbervar avg := round((curr+prev)/2);
totext((prev-avg),0,"")

-LB

 

[AnnetteB1305]

No, nothing wrong with that sorry meant no offence was being facetious. I'll give that a whirl and let you know how I get on

 

[AnnetteB1305]

That's great thanks again for your help

Annette