Average Daily Balance Question 1

[KristieLee1]

I am attempting to calculate the average daily balance (for gallons). I can get this to work by using the running total function (to sum the gallons by day) and dividing the running total by the number of days elapsed in the month to get the average daily balance(runningtotal/#of elapsed days in month).
This works, and gives me the correct information for every day. However, I just need the average daily balance based on today.
Lets say I've sold 186k gallons in 10 days. I don't want to see each day displayed, I just want to see one row that shows the average daily balance for all 10 days as of today.

I have this...

date gals runtotgals avg daily bal (gal)
7/1/09 7,562 7,562 7,562
7/3/09 14,929 22,491 7,497
7/6/09 14,950 37,441 6,240
7/7/09 30,268 67,709 9,673
7/8/09 81,020 148,729 18,591
7/9/09 22,488 171,217 19,024
7/10/09 14,876 186,093 18,609

But only want to display the information on the 10th. Whenever I try to remove (cut) any of the above items out of my report, it returns crappy results.

Ideas??? Sorry if I'm confusing...its just a product...of well..me...being confused. Thanks in advance for any help

 

[blom0344]

I do not see the problem
Why not fetch the 186k gallons and divide by the number of days?
The running - total is only needed as an object in it's own right, you can simply put an aggregate on gals and totalize the individual values (and then divide by the factor you need). This returns 1 record in your example.
You can even make the range dynamic by using date prompts for start and enddate..

Ties Blom

 

[KristieLee1]

Do you mean I don't need the running total at all, that I can just sum the gallons and divide by the number of days?

I'm sorry...I'm still very new...and math isn't my strongest suit yet...I'm sorry

 

[blom0344]

Exactly.. The running-total has only use for displaying information at the detail level. If you are after a single outcome (display 1 record) then it is a simple matter of aggregating the measure to the level you need and making the division to calculate the average.

Adding running totals is even potentially bad for performance cause the Cognos server needs to compute them AFTER the query is executed on the database. So, do not use them unless you want to display them in the report itself!

Ties Blom