Expression Total using If and Else condition

[Anonymous]

Hi All,

I have been tasked to amend the Qlikview pivot table where I need to put an If and Else condition on the expression where it says if Name = 'ABC' than do calculation this way else the other way. The expression with If and Else is working fine at row level is calculation basis the names however the total shown is ignoring the If and Else and considering only the else part. Example given below.

Name     CalculatedField

AB           10

BC           10

ABC         5

Total        30

Ideally the total should be 25 as one of the row has ABC name but instead the total is ignoring If part which will consider 5 but taking this into Else part which is considering 10.

Any help will be appreciated.

Thanks a lot in advance!

[ogautier62 · Accepted solution]

Hi,

maybe you have used total of expression option for your expression,

and with if(xxxxxx) it can't work

check option : sum of rows

regards

[Anonymous]

Hi Olivier Gautier, Thanks you are right by changing it to Sum of rows this worked.

[Anonymous]

Any idea how this can be achieved when only giving one row with totals? If changed to sum of rows it gives 0 as there is no row to do sum on.

[ogautier62]

in this case you have to use an expression beginning with an aggregator, and not begin with if

so replace if( abc, sum(this way), sum(that way)

by :

sum(if(abc, this way, that way)) if it possible

or make two columns (not visible)

with sum({<Name = {ABC} >}    this way)

id for the other

and a total column = col1 + col2

so you can use total of expression

[Anonymous]

Yes, it worked... I chose to go with option two and it gave me the results I wanted. Thanks Oiliver!!