topic Expression Total not matching to row values in Water Cooler

Expression Total not matching to row values

Fri, 04 May 2012 19:25:33 GMT

Hi, for example if i take the the calulcation coloum3/coloum1 i get the correct values on a row by row basis , yet when using the expression total functionality the result is different to the physical caluclation.

For example

5968.16 / 1808.2 = 3.30061

Yet in QV the expression total result is 3.63

Our actual expression is

num(sum( {$< tcat={0} , [Finalised Date]={"<=$(=_vNull_Fin_Date_Excl) "} , _Year={"$(=MAXSTRING(Year))"} , _sequence={"<=$(=Max(Sequence))"} >} DelayCount )

/

sum( {$< tcat={0} , [Finalised Date]={"<=$(=_vNull_Fin_Date_Excl) "} , _Year={"$(=MAXSTRING(Year))"} , _sequence={"<=$(=Max(Sequence))"} >} EntryCount

) , '#,##0.0' )

Expression Total not matching to row values

swuehl — Sat, 05 May 2012 16:53:41 GMT

There can indeed be a difference between an expression total and a sum-of-rows total.

For example, if your measure values might be included / accounted to different dimension values, you will get differing results.

Have you tried using the total mode sum-of-rows in a straight table or calculating a sum-of-rows using aggr() function in a pivot table (there is a chapter in the Help about latter method)?

Expression Total not matching to row values

jonathandienst — Sat, 05 May 2012 18:46:46 GMT

The difference in sum of rows and expression typically happens when you have something like a ratio (as in your example), that is not additive. For example, consider two data points:

1/3 and 2/3

When added together you will get 1/2+2/3 = 1, but when calculated with the expression, you will get (1+2)/(3+3) = 1/2. The expression is mathematically "correct", and is not arithmetically the same as the sum of rows.

Hope that helps

Jonathan

Expression Total not matching to row values

Sat, 05 May 2012 19:33:22 GMT

Hi,

If the word total occurs before an expression, the calculation will be made over all possible values given the

current selections, but disregarding the chart dimensions.

Where are you using total keyword? Is it in column 1 which you use to divide column 3.

Please explain in detail.

Thanks,

Anosh

Expression Total not matching to row values

Sat, 05 May 2012 20:57:41 GMT

Thanks all , i'm making progress ..... for the final step i need to add a calculcation against two expressions in my existing table which consists of many rows of data. Example being

Hrs (expression total) = 20

delay (sum of rows) = 100

Total Delay / Expression Total Hrs = 5

Is this possible, if so could i get a steer on possible syntax ?

I'm using a simple expression =([delay]/hrs) and am struggling to obtain the total average of 5 and instead can only obtain a sum of rows for the caclulation, when i select expression total it is null.

Re: Expression Total not matching to row values

Sat, 05 May 2012 20:58:57 GMT

Thanks all , i'm making progress ..... for the final step i need to add a calculcation against two expressions in my existing table which consists of many rows of data. Example being

Hrs (expression total) = 20

delay (sum of rows) = 100

Total Delay / Expression Total Hrs = 5

Is this possible, if so could i get a steer on possible syntax ?

Expression Total not matching to row values

Sun, 06 May 2012 05:42:09 GMT

May be if i understood your problem, you can try sum(total [delay])/sum(total [hrs])

If it is not working please provide screenshots or eg qvw of what you are trying to do.

Hope this help,

Anosh