topic Expression Issue (Subtract a number from a count IF) in QlikView

Expression Issue (Subtract a number from a count IF)

MalcolmCICWF — Tue, 12 Feb 2013 23:11:12 GMT

I have this count in my report and one of my end users wants to remove 222,723 accounts if the "Tier" and "date" equal what I have below. I must have something arrange incorrectly because it will not remove this volume of accounts from the total. I have it commented out for now as shown below. Could someone help me arrange this correctly?

Re: Expression Issue (Subtract a number from a count IF)

phaneendra_kunc — Tue, 12 Feb 2013 23:55:22 GMT

Try this Expression .

=if(concat(Tier,',') like 'Quint*' and Concat([Date Added Month] like '01*',count(Distinct [Account ID]) - 22723) ,count(Distinct [Account ID]))

Re: Expression Issue (Subtract a number from a count IF)

MalcolmCICWF — Wed, 13 Feb 2013 14:42:15 GMT

This is not working

I have never used the concat functions to do anything like this, is it necessary? Is it not more simple to count the distinct Account ID but if it is Tier 'Quint' and Sate Added Month '01', then subtract? I have similar things like this in the past in the script itself, would it be easier to try and add it here?

Re: Expression Issue (Subtract a number from a count IF)

phaneendra_kunc — Wed, 13 Feb 2013 16:33:02 GMT

Perhaps a sample applicaiton will help me to suggest you an answer.. Above expression worked for me..

what i have noticed is that if i am using "IF" in the chart expression and the conditions on the columns...it is not populating with any result..unless if i keep the columns that i am filtering on into the chart...

Phani

Re: Expression Issue (Subtract a number from a count IF)

MalcolmCICWF — Wed, 13 Feb 2013 16:44:05 GMT

I actually figured it out and got it to work with this simple expression:

(Tier='Quint' and [Date Added Month]='01',count(distinct [Account ID])-22723,count(distinct [Account ID]))