topic Wrong total in total line in QlikView

Wrong total in total line

Fri, 12 Feb 2010 15:50:56 GMT

in my attachment you can see that the total is not correct

i like to use the if statement because in my real file the formula is very complex.

How are totals calculated in QV? why is the sum not correct?

is use this formula

(User_id=1,0,Sum(number))

is it also possible to change the sum in avg??

Wrong total in total line

disqr_rm — Fri, 12 Feb 2010 16:36:17 GMT

If you have user_id in the dimension, why do you need to use it in the expression under if condition? May be I am missing what oyu are trying to achieve.

To me seems that if you just put sum(number) in the expression, it will give you what you are looking for.

Wrong total in total line

Fri, 12 Feb 2010 16:45:05 GMT

Its the way its aggregating from the dataset.

Try sum(if(user_id=1,0,number))

Wrong total in total line

Fri, 12 Feb 2010 18:27:06 GMT

thnx this works perfect!

Wrong total in total line

Fri, 12 Feb 2010 18:32:19 GMT

and how about my original if-function?

if (CODA_ASSETBOOK_DEPNSTARTYEAR>year(Now())-1,0,if (CODA_ASSETBOOK_DEPNSTARTYEAR+(CODA_ASSETBOOK_ASSETLIFE/12)>year(Now()),(sum(CODA_ASSETBOOK_COST)-sum(CODA_ASSETBOOK_RESIDVALUEASSET))/((CODA_ASSETBOOK_ASSETLIFE/12)-(if(year(now())-CODA_ASSETBOOK_DEPNSTARTYEAR>(CODA_ASSETBOOK_ASSETLIFE/12), 0, year(now())-CODA_ASSETBOOK_DEPNSTARTYEAR)))
,0))

the last else 0 (,0) is the problem!

Wrong total in total line

Fri, 12 Feb 2010 18:38:50 GMT

What is it returning currently?

Wrong total in total line

Fri, 12 Feb 2010 18:59:55 GMT

this is the result , line with activum COMP0003 has value 0, and the total sum is also 0, so there is a problem

Wrong total in total line

Fri, 12 Feb 2010 19:01:34 GMT

sorry, column 2009 is the column to look at

Wrong total in total line

Fri, 12 Feb 2010 20:12:28 GMT

Is that the only row that is incorrect?

Wrong total in total line

Fri, 12 Feb 2010 20:24:04 GMT

This row is correct, so there has to be a value 0 (zero)

but because there is value 0, the total line (0) is not correct, this is my problem

Wrong total in total line

Fri, 12 Feb 2010 20:30:52 GMT

It is hard to tell without a sample application to work with, but it is usually aggregation functions nested inside the IF that cause the problem. Can you take the sums off of CODA_ASSETBOOK_COST and CODA_ASSETBOOK_RESIDVALUEASSET and place a sum around the entire IF statement at the top level?

Wrong total in total line

Fri, 12 Feb 2010 20:39:59 GMT

You are my STAR! *

that's the solution

thank you very much, and thanx for this great community!!

Wrong total in total line

Sat, 13 Feb 2010 20:34:53 GMT

One more question

in my picture you see 2 pivot tables, is it possible to have the total line like in the second table, or is it only possible to have a sum in the total line??