• Status: Solved
• Priority: Medium
• Security: Public
• Views: 582

# Cross-tab and Line Graphs with Averages

Having an issue with using an average formula in my cross-tabs and line graphs.

My formula to calculate average talk time is {table.talktime} / {table.interactions}  where talktime is the total talk time in seconds and interactions is the number of interactions being handled.

The table looks similar to this:

INTERVAL              TALKTIME              INTERACTIONS
06:00:00 1/1/2011      0                                0
07:00:00 1/1/2011      37250                        36
08:00:00 1/1/2011      12697                        13
09:00:00 1/1/2011      1462                           2

Giving me a total talk time of 51,409 seconds across 51 interactions.  Therefore, the average is about 1008.02 seconds.

If I drop my formula in the "summarized field" section of the crosstab, my average is displayed as  131.12.  If I change the summary to a weighted average with table.interactions it's 759.04.  Both are wrong.

However, if I simply drop {table.talktime} into the "summarized field" section with a weighted average with table.interactions, I get closer with 1600.76.  But it's still not right.  What can I do?

And then line graphs - I don't know how to do this either.  Do i need to change my average formula to take in a "group by" value?
0
c0fee
• 9
• 6
• 6
3 Solutions

Author Commented:
BTW, I'm using CR XI.
My column in table.Interval.  I have nothing in the rows - only in the summarized fields and column.
0

Commented:
When you use your average the cross tab then takes 0, 1034, 977, 731 this is then used to sum and divide by 4

I think the only way you will be able to do this is with a manual crosstab where you control the formulas.

mlmcc
0

Author Commented:
That's depressing.  By manual crosstab, you mean I have to manually create groups and headers - a normal report?

If my headers have to be a each date in a given datetime range, between 1-31 days, do you have any advice or sample templates I can reference?  How can I iterate through a date range like 1/10/2011 - 2/5/2011 and build the headers and corresponding average talk time formula?

What about building a line graph that shows average talk time per day?
0

Commented:
What is the raw data?
Is it the total time and total calls?

Yes, that is basically what a manual cross tab is.

There is a trick for getting the missing dates into the report.

mlmcc
0

Author Commented:
Yes, that's the raw data.  The date times are stored in hourly intervals per day: 2011-01-01 12:00:00, 2011-01-01 13:00:00, 2011-01-01 14:00:00, etc.  So I will need to group by day (my headers). For each interval, there is a total time field and a total calls field.

From that data, I will then need to have a corresponding line graph for the average time.

thank you!!
0

Author Commented:
mlmcc - do you need any other info from me?  Appreciate the help. Thanks.
0

Commented:
> However, if I simply drop {table.talktime} into the "summarized field"
> section with a weighted average with table.interactions, I get closer
> with 1600.76.

Did you also try a simple average (not weighted) with those two fields?  It seems like that would work.

James
0

Author Commented:
I've tried.  But I think it's taking the average across all of the records instead of averaging it across the sum of interactions (talktime / interactions).  Attached is a sample data for three of the days.

If I need to create a sproc or view to return the data so the average can work in a crosstab, let me know.   My concern is that it's going to be a massive amount of work to build a report that has up to 31 days as headers and 25 formulas for each day...

If someone can give me an example of how to create a manual crosstab with the attached data that would be great.  Provide me the sum of interactions and the average talktime per interaction (talktime/interactions) for each day (headers), I'd appreciate it.
SampleData.xlsx
0

Author Commented:
I was trying to play around using arrays, and I think I've got the pseud-code right - but I need some help:
* Create a datetimevar array to hold all the dates in my {?daterange} parameter
* Either create a group that iterates through each date to build the array.
* In each group, build my formulas: total interactions, average talk time, etc.
* Assign the results to a corresponding array: numbervar array ints, numbervar array talk, etc.
* Create two report footer sections. Display each value of the datetimevar array using 31 "header" formulas where @header1 is datetimearray[1], @header2 is datetimearray[2], etc.
* Likewise the second footer will display all my formulas (31 of each): @interactions1 = interactionsarray [1], @talktime1 = talktimearray [1], @interaction2 = interaction[2], @talktime2 = talktimearray[2], etc.

Would that work?  If so, can someone provide me an example of how to build the array formula and then how to call it?  Or is there a better way?

My datetimevar array looks like this:

local dateTimeVar array daterange;
if onfirstrecord then
ReDim daterange [1]
else
ReDim Preserve daterange [ubound(daterange)+1];
daterange[ubound(daterange)] := date({table.Interval});

But I can't successfully reference my array in a second formula afterwards...
0

Commented:
0

Commented:
Argh.  I wrote a reply, but when I tried to post it, the site was down and my reply was gone.  <sigh>  Then again, I suppose it wouldn't be such a hassle if I didn't write so much.  :-)  Anyway, starting over ...

> But I think it's taking the average across all of the records instead of
> averaging it across the sum of interactions (talktime / interactions).

Yeah, it would be the average from all records.  I was under the impression that the 4 lines in your original post for 06:00:00, 07:00:00, 08:00:00 and 09:00:00 were 4 records, not some kind of totals.

As for your array, you made daterange a Local variable, which means that it will only exist within that formula.  When the formula ends, it's gone.  You need to declare it Global or Shared.  Shared would probably be simpler (Global may require one or two other (minor) changes before it would work properly).  Just make sure that you also declare the variable as Shared in any other formula that uses it.

As for your overall plan, if the basic problem is that you need to calculate averages or other summaries based on totals, then the simplest thing would probably be if you could create a stored procedure, view or CR command (manual query stored in the report) that produced the totals, so that the report didn't have to.  Then you could use those totals in a cross-tab or chart, do averages on them, etc.  You can create a manual cross-tab using formulas for the columns, etc., but you can't create a chart that way (except maybe for a simple bar chart).  It's possible to create a chart using an array and a subreport, but it would be much simpler if you could "massage" the data before it gets to the report, so that you could use a regular cross-tab and chart.

James
0

Commented:
Shared have the same issues as globals and probably should be declared in the report header.
The only advantage of shared over global is with shared you get an implied WhilePrintingRecords since they have to be evaluated in the printing pass.

mlmcc
0

Author Commented:
Is there an advantage to declaring a Shared variable vs. just declaring a local variable and using whileprintingrecords?  I was able to use and recall the local datetimevar array by using whileprinting records in the formulas.  Is that less efficient?
0

Commented:
Local variables are only in that formula
You want
Shared dateTimeVar array daterange;
if onfirstrecord then
ReDim daterange [1]
else
ReDim Preserve daterange [ubound(daterange)+1];
daterange[ubound(daterange)] := date({table.Interval});

OR

WhilePrintingRecords;
Global dateTimeVar array daterange;
if onfirstrecord then
ReDim daterange [1]
else
ReDim Preserve daterange [ubound(daterange)+1];
daterange[ubound(daterange)] := date({table.Interval});

mlmcc
0

Commented:
> I was able to use and recall the local datetimevar array
> by using whileprinting records in the formulas.

Only if you're declaring and using it in the same formula.  As I, and mlmcc, said, Local variables only exist in a single formula.  When that formula ends, the variable is gone.  If you declare a Local variable with the same name in another formula, it will be a new variable and will have the default value for that data type, until you set it to something else in that formula.  If you've found an exception to that, I'd love to see it.

> Is that less efficient?

It could be.  Let's say that you have some lines in a formula that create an array and fill it with dates, and you are only going use those dates for reference and not change them.  You could make that array a Local variable and repeat those lines in every formula that needs that array, so every formula would create the array, fill it with dates, and then use the array.  It would be more efficient to make the variable Global or Shared and put a formula in the report header that creates and fills the array.  Then the formulas that need to reference those dates just need to declare the Global/Shared variable and the dates will already be there, so they don't have to recreate and refill the array every time.

Now, while that would be more efficient, whether or not it would actually have a noticeable/significant effect on the report's performance is another matter.  In many, if not most, cases, I doubt that you'd actually see any difference.  But if the formula(s) was(were) being executed enough, I suppose you could.

FWIW, I handle this kind of thing by using a formula in the report header to set the variables, just because I know that it should be more efficient, even if I don't really expect it to make any real difference in the report's performance.

James
0

Commented:
Just to be clear, when I talked about creating an array and filling it with dates, I was talking about a formula that fills the entire array at one time.  For example, a formula that sets the first date in the array to 01/01/2011, and then uses a loop to increment the date and set the elements after that to 01/02, 01/03, etc.  You could use a formula like that in the report header to fill a Global/Shared array, and then reference that array in other formulas.

If you're actually filling the array with values from your records, that would have to be done in a section after the report header, as the records are read, and you could not use a Local array variable for that, because that formula will be re-executed each time that section is processed, and a Local variable (your array) will be restarted from scratch with each new value.  In that context, what you said about getting the Local array to work makes no sense.

James
0

Author Commented:
OK. Thanks.  My fault, I thought the default variable declaration (sans scope) was local.  I guess it's global and it all makes sense.

I ended up creating a date group with the array formula placed in the group header:
whileprintingrecords;
global dateTimeVar array daterange;

if onfirstrecord then
ReDim daterange [1]
else
ReDim Preserve daterange [ubound(daterange)+1];

daterange[ubound(daterange)] := {@Group1};

Then created date1, date2,.....date31 formulas and placed them in a report footer:
whileprintingrecords;
dateTimeVar array daterange;
if ubound(daterange) >= 1 then
daterange[1];

Likewise, I created the averagetalk array formula, averagetalk1..averagetalk31 and placed them underneath my date headers.  I'll be doing this for 20 formulas total.

It's all so manual, but it was the only way I could figure out how to create a manual crosstab with a varied range of headers.  The report will end up having 600-700 formulas.  Yikes!  Hopefully that doesn't raise any red flags in terms of report execution performance....
0

Commented:
Default scope is global.  That is one reason why in all formulas I always specify the scope so it is obvious.

I agree it is a difficult way to do it.  I find performance is more impacted by the databse retrieval that by the formulas being used.

Depending on how the report is to be used you may be able to run it ahead of when it is needed and save it with data so the load isn't so bad.

mlmcc
0

Commented:
I don't know.  That's a lot of formulas.  :-)  Off hand, my biggest concern would be hitting some basic limit on the number of formulas or something like that, or possibly memory consumption.  If you don't run into something like that, I think it will probably be OK.  Maintenance could be fun though.  :-)

James
0

Author Commented:
Accepted my own comment as part of the solution since I came up with the formula syntax.  However, awarded points to both mlmcc and james0628 because I had to leverage their knowledge and advice.
0

Commented:
I'm glad I could help.  I wish there had been a better solution, but there are some things that CR just does not handle well.

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.