Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression Total not matching to row values

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

6 Replies
swuehl
MVP
MVP

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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