Solved

Cross-tab and Line Graphs with Averages

Posted on 2011-03-03
21
565 Views
Last Modified: 2012-05-11
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
Comment
Question by:c0fee
  • 9
  • 6
  • 6
21 Comments
 
LVL 2

Author Comment

by:c0fee
ID: 35031280
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 35031479
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
 
LVL 2

Author Comment

by:c0fee
ID: 35031632
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35031747
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
 
LVL 2

Author Comment

by:c0fee
ID: 35031829
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
 
LVL 2

Author Comment

by:c0fee
ID: 35041555
mlmcc - do you need any other info from me?  Appreciate the help. Thanks.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35043347
 > 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
 
LVL 2

Author Comment

by:c0fee
ID: 35043697
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
 
LVL 2

Accepted Solution

by:
c0fee earned 0 total points
ID: 35045065
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35047100
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:James0628
ID: 35048224
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35048307
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
 
LVL 2

Author Comment

by:c0fee
ID: 35049152
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35051652
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 35066905
 > 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
 
LVL 34

Expert Comment

by:James0628
ID: 35066926
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
 
LVL 2

Author Comment

by:c0fee
ID: 35069717
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35071484
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
 
LVL 34

Expert Comment

by:James0628
ID: 35086834
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
 
LVL 2

Author Closing Comment

by:c0fee
ID: 35120657
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
 
LVL 34

Expert Comment

by:James0628
ID: 35136100
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now