Average of certain records

Applications Grid / Reports
#1

Good afternoon,

I have two columns: {Count}, {Time}

{Count} can either = 1 or 0, and {Time} always has a certain number of seconds in it.

Ultimately I need sum({Count}) / sum({Time}) which would equal average seconds for the day.

I can’t figure out how to do this using the Scriptcase summary feature for an entire day. I can get all the totals of those columns just fine. I can get the average of {Time}, but that counts ALL the records, verse using the total.
How do I get an average of {Time} where it’s only divided by the TOTAL of {Count}, not the total record count?

Hopefully that question makes sense. Any help would be appreciated!

Cheers,

Mark.

#2

Create a Field calles for example AvgTime and onRecord event give it value with your formula

#3

Thanks Giu,

I admit I am still a beginner here, but I’ve already got 3 fields being calculated ‘on record’ as you suggest - which are working fine. My application is configured to only show a summary by month. So I have 3 months of records for example, that are totaled / summarized in the app using the Total and Summary features of Scriptcase. Everything works fine except the AvgTime. I tried (based on my understanding of) what you suggested, but couldn’t get it to work. In the On Record…

If I put {AvgTime} = sum({Count})/sum({Time}); I get an immediate error like “Fatal error: Call to undefined function sum() …”

If I just do {AvgTime} = {Count}/{Time}; It spins for a long time, and then generates division by zero errors, because many of the {count} are equal to zero.

But as I was typing this all out, I wondered if I could generate an app with the totals from the start, not using the summary e.t.c, but by putting my formulas in the creation script at the beginning of app generation. So I did:

SELECT sum({Count})/sum({Time}) from tblStats where Cust = ‘1234567’
…but that failed and the error I received was: Invalid argument supplied for foreach()

So my ignorance has me lost here and I’m not sure what else to try? Thanks again for your suggestion and any further advice you would be able to offer!

Mark.

#4

About division by zero is not a SC problem but maths problem :smiley:

You can always do a check before the calc …

{AvgTime} = 0;
if({Time}!=0) {
{AvgTime} = {Count}/{Time};
}

About sum({Count})/sum({Time}); … sum is not a PHP function this is the reason of the error, and you don’t need it. You can create on SC a SUM on the field AvgTime to be totalized automatically. For clarification, on your onRecord you just need to give value to the field, because onRecord event fires on every row, for this reason you don’t need to sum it, just put the value of this record, and let SC to calc the total with TOTALS propertis of your grid.

#5

Wow, I’m learning more every day! Thank you Giu and I appreciate your help very much! Truth be told, either I’m not explaining myself well at all, or I’m completely not getting it. For clarity’s sake, I’m going to change the naming conventions, because I’ve realized it just introduces confusion (probably mostly on my part! =). Instead of the column ‘Count’, I’m going to use ‘Item’ instead. Count gets too confusing when we’re referencing count of records e.t.c.

Here’s an example:

Item Time

1 20
0 30
0 18
1 5
1 22

3 95

If we sum the ‘Time’, and divide by the sum of ‘Item’, we have: 95 / 3 = 31.6 (which is the average I’m looking for).
If I just do the usual Scriptcase Avg on Time, it divides the 95 sum by the total count of records (5), giving an average of 19 - which is not correct.

As you mentioned, the ‘On Record’ doesn’t like dividing by zero…but I ‘believe’ the formula you’re suggesting would ignore the record completely if the Item = 0…however I can’t ignore the Time associated with that 0 record - I need all the Time totaled for the month (thousands of records) and divided by the sum of the field ‘Item’.

Did I completely misunderstand what your previous post is referencing, or did I not explain myself properly the first time? I can do it in straight SQL code just fine, and it seems like it should be simple to do based on everything Scriptcase can do, but I just can’t seem to grasp how to get this done. Sorry for being a noob.

Thanks very much,

Mark.

#6

If we sum the ‘Time’, and divide by the sum of ‘Item’, we have: 95 / 3 = 31.6 (which is the average I’m looking for).
If I just do the usual Scriptcase Avg on Time, it divides the 95 sum by the total count of records (5), giving an average of 19 - which is not correct.

Sum don’t count records, sum values.

As you mentioned, the ‘On Record’ doesn’t like dividing by zero…but I ‘believe’ the formula you’re suggesting would ignore the record completely if the Item = 0…however I can’t ignore the Time associated with that 0 record - I need all the Time totaled for the month (thousands of records) and divided by the sum of the field ‘Item’.

Was just an example. I understood you want to do your formula on every record.

Did I completely misunderstand what your previous post is referencing, or did I not explain myself properly the first time? I can do it in straight SQL code just fine, and it seems like it should be simple to do based on everything Scriptcase can do, but I just can’t seem to grasp how to get this done. Sorry for being a noob.

Because maybe I don’t understood correctly. I don’t know either if you want to show this value on a alert, or what, otherwise.

One option is using the Totals propertys of the Grid, as Sum for Item and Time, and then, onFooter event, you can access the value with special summary vars {sum_(your field)}, in your example {sum_item} and {sum_time} , then you just have to do something like:

$average = {sum_time}/{sum_item}
sc_alert ($average);

Another option is to have 2 global vars (set it as out), [glo_time] and [glo_item] and in your onRecord event do.


[glo_time] += {time};
[glo_item] += {item};

Once grid is loaded you will have in this 2 global variables all time and items to do your calcs.

As I mentioned, I don’t know exactly where you want to show this value or what do with it, I’m just “blind” telling you different ways to get the value you want :wink:

#7

Giu,

Thanks again for your time! and I understand completely on the guessing game - it’s difficult to relay a goal in simple text. I’ve been so busy on other items today, I haven’t had a chance to try your suggestions. I took a screen shot (edited a bit to match what we’re discussing) so you can see what my ultimate goal is.

So as you can see, it would ‘appear’ to be a simple average type setup. (The average isn’t working right now, but if it were, the averages would be 10.9, 17.8 and 14.2 respectively) I have literally thousands of records per month, that I’m trying to get summarized into a monthly grid like this. Getting that average has been elusive <grin> but I will try your suggestions out and see if I can get it to look like the image.

Thanks again SO much for your time. I’ve certainly learnt a lot already and it’s good working with someone who knows what he’s doing…unlike myself! :wink:

Thanks very much,

Mark.

Summary.png

#8

I couldn’t get either of the suggestions to work and provide a column like the image above. Ideally, there would be an “Add Field” in the “Totals” area of Scriptcase, where I could simply put {AvgTime} = {Time_sum}/{Item_sum}

Playing with similar numbers in Excel, I ‘thought’ I had a solution. I created a new column in Excel, and wrote a formula on each row that says if ‘Item’ = 0, put a ‘NULL’ value, and if ‘Item’ = 1, put the ‘Time’ Value. Then at the bottom of that column, I did the AVG of that new field, and it worked! The AvgTime was correct. I assume a NULL value in Excel does not get ‘counted’, only rows with numbers in them.

So I went off to see if it would work the same way in Scriptcase.

With my OnRecord event, (Thanks to Giu’s example above) I have:

{AvgTime} = null;
if({Item}!=0) {
{AvgTime} = {Time};
}

On the “Detail” view, AvgTime is empty unless Item = 1, and if so, the Time is correctly in the new field…Perfect!! But unfortunately the “Average” function doesn’t work like Excel, and still gives me the average based on ALL records, and not just the average of the records with Time in them. So I’m back to square one. I guess I could create another application that calls for only records that have Item = 1 and calculate averages off of those, but then that’s two applications that have to run at once, and I’d also have to figure out how to present both applications at once, or have a link of sorts. I know people laugh at MS Access, but I can do this stuff until the cows come home in Access. It seems like the image I posted above should be simple to produce… but clearly it isn’t. At least not with my limited knowledge…

Thanks very much,

Mark.