Use AVG in Totals in List options

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Use AVG in Totals in List options

Postby cjbates » Wed Jun 27, 2012 8:05 pm

I have a question about the way you are calculating total amounts for averages on groupings. I am using AVG to calculate an average amount for a report with multiple groupings. At the top most group, the AVG amount appears to be the average for all of the records in the dataset. But when I drill down, there is a subtotal AVG which in my mind should match the upper level grouping (see attachment). However, it is an average of the average amounts for each of the rows in my next grouping level (do the math and you can see that the 452 in the drill down subtotal is an averate of the 5 rows (add up the AVG HAP in the 5 rows and divide by 5) instead of an overall average that matches the average in the top most grouping. Intuitively I would excect the totals on the subgrouping to match the row values on the next highest grouping and they do for othet total types like Sum or Count, but not for AVG.
Furthermore, I can suppress the Column Grand totals using List Options, but this does not suppress these totals in the lower groupings. How do I hide this incorrect overall AVG figure in the lower half?
Thanks
Cynthia
Attachments
dynamicaiAvgQuestion.png
dynamicaiAvgQuestion.png (89.67 KiB) Viewed 29037 times
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Use AVG in Totals in List options

Postby Bob Cergol » Wed Jun 27, 2012 9:12 pm

Cynthia,

Can we see how you made the average?
Did you make you own plus column and specify the column type as "Total formula"?
Or did you add an existing column to totals, and set its type to "avg."?

Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Use AVG in Totals in List options

Postby Bob Cergol » Wed Jun 27, 2012 9:29 pm

Cynthia,

The screen shot below shows how Dynamic AI's "avg" total type simply averages the values in the column.

I think what you want to do is make a +column as a "total" type and then the calculation will be made at the actual level of the data, i.e. calculate at the group total level, etc.

Avg.Sale = Amount used as total and type set to "avg.(This is avg. the numbers in the column which are avg. themselves!
Avg.Amt. = +Column using Amount divided by count. (This is the avg. you want.)

Its easier to learn the syntax for some of these formulas by using the on-screen formulat editor to make them!

Avg_Total_Types.png
Avg_Total_Types.png (78.33 KiB) Viewed 29031 times

Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Use AVG in Totals in List options

Postby cjbates » Thu Jul 19, 2012 7:27 pm

Hi Bob,

I think understand what you mean by calculating the avg....but my formula is not working (Im getting no results). Please look at my screen shot and tell me what is not correct? Thansk.
Cynthia
Attachments
avghapformula.png
avghapformula.png (103.86 KiB) Viewed 29002 times
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Use AVG in Totals in List options

Postby Bob Cergol » Thu Jul 19, 2012 7:47 pm

I guess you must be getting an error because I see an extra + sign in the formula.
I think this is probably what you want:
( [[tr_hap]] + [[tr_uap]] ) / [[# Tenants@COU]]
Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Use AVG in Totals in List options

Postby cjbates » Mon Jul 23, 2012 8:01 pm

Hi Bob, I removed the extra + but still receive no results (field is blank, there is no error nor is there a 0).

Cynthia
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm


Return to Technical Support

cron