Expression Total not matching to row values

[#1]

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' )

[swuehl]

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)?

[jonathandienst]

Hi

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

[#4]

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

[#5]

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.

kr

[#6]

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.

kr

[#7]

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