topic Re: Expression giving incorrect total in QlikView

Expression giving incorrect total

Wed, 30 May 2012 14:39:56 GMT

I have the following conditional expression which does not provide a correct total but does provide the line detail I want:

=if(ARTRANSTYPE <> 'Adjustment'
,
 sum({$<Phase=,TRANSTYPE=, ACCT_DESC=>}TRANSACTIONAMT)
,
 sum({$<Phase=,TRANSTYPE=, ACCT_DESC=>}INVNETTOTAMT) - sum({$<Phase=,TRANSTYPE=, ACCT_DESC=>}TRANSACTIONAMT)
)

The problem is that I'm actually receiving a total as if I wrote the expression like this:

sum({$<Phase=,TRANSTYPE=, ACCT_DESC=>}TRANSACTIONAMT)

Why does it appear to being ignoring the "else" part of the if statement when totalling?

What am I missing in my expression? Thanking you so much in advance.

Expression giving incorrect total

Anonymous — Wed, 30 May 2012 14:43:36 GMT

In the Expressions tab of the object properties under Total Mode, are you using "Expression Total" or the selectable field underneath? I would try playing with those possibly using the Sum of Rows option.

Re: Expression giving incorrect total

Wed, 30 May 2012 14:52:40 GMT

Hi Aaron:

Thanks but it's disabled and I can't change it... but it is set on "Expression Total". I'm using a pivot chart.

Re: Expression giving incorrect total

RedSky001 — Wed, 30 May 2012 15:48:54 GMT

I expect you have not got the transaction key as a dimension.

Therefore to calculate the Total qlikview will perform one calculaiton.

What is the total ARTRANSTYPE ? It can't do this so it just does sum({$<Phase=,TRANSTYPE=, ACCT_DESC=>}TRANSACTIONAMT)

You'd need to do something like this:

So even for the Total qlikview will know to look at the TRANSTYPE for every ID.

Sum(

AGGR(

if(ARTRANSTYPE = 'Adjustment'

sum(INVNETTOTAMT) - sum(TRANSACTIONAMT)

(TRANSACTIONAMT)

)

,ID)

)

Mark

Re: Expression giving incorrect total

Wed, 30 May 2012 16:02:09 GMT

Thanks so much Mark!

I used my dimension which is "invoice" where you have "ID" and it works great now.

Much appreciated.