topic Re: Expression Total using If and Else condition in QlikView

Expression Total using If and Else condition

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Expression Total using If and Else condition

ogautier62 — Thu, 20 Sep 2018 13:02:34 GMT

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

Re: Expression Total using If and Else condition

Anonymous — Thu, 20 Sep 2018 13:46:55 GMT

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

Re: Expression Total using If and Else condition

Anonymous — Thu, 20 Sep 2018 14:16:15 GMT

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.

Re: Expression Total using If and Else condition

ogautier62 — Thu, 20 Sep 2018 15:06:57 GMT

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

Re: Expression Total using If and Else condition

Anonymous — Fri, 21 Sep 2018 13:03:53 GMT

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