Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
MalcolmCICWF
Contributor II

Expression Issue (Subtract a number from a count IF)

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?

Capture.JPG

Tags (1)
1 Solution

Accepted Solutions
MalcolmCICWF
Contributor II

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

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

 

if

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

4 Replies
phaneendra_kunc
Valued Contributor III

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

Try this Expression .

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

MalcolmCICWF
Contributor II

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

This is not working

Capture.JPG

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?

phaneendra_kunc
Valued Contributor III

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

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

MalcolmCICWF
Contributor II

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

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

 

if

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

Community Browser