topic Re: Using multiple parameters in an expression statement in QlikView

Using multiple parameters in an expression statement

Anonymous — Thu, 10 Jul 2014 15:49:36 GMT

I know how to test a single parameter in an expression statement (example: Count the number of accounts with Status code = CLS would be represented as : =Count ({$<CURR_STATCD={'CLS'}>}ACCTNBR)

How do you enter multiple conditions in this statement? For example how would I count the number of accounts with Status = CLS and Opendate >= 1/1/2014? How is that represented in an expression statement?

Thanks for your help.

Re: Using multiple parameters in an expression statement

alexandros17 — Thu, 10 Jul 2014 15:50:53 GMT

=Count ({$<CURR_STATCD={'CLS'}, Opendate = {'>= 1/1/2014'}>} ACCTNBR)

let me know

Re: Using multiple parameters in an expression statement

Anonymous — Thu, 10 Jul 2014 16:53:37 GMT

Thanks. The expressions is valid (I am seeing "Expression OK" after I enter it. I don't think it likes the " {'>= 1/1/2014'}>} " condition after OPENDATE=.

I tried modified my original statement as " =Count ({$<CURR_STATCD={' <> CLS'}>}ACCTNBR) to count all records with a status code not equal to CLS. I got nothing returned so I don't think that is the correct syntax (and yes there are other values and no filters set).

Any other thoughts?

Re: Using multiple parameters in an expression statement

jerem1234 — Thu, 10 Jul 2014 17:19:10 GMT

If you don't want CLS try:

=Count ({$<CURR_STATCD-={'CLS'}>}ACCTNBR)

and Alessandro Saccone's formula should be right for the date. Maybe your dates aren't in a date format?

Hope this helps!

Re: Using multiple parameters in an expression statement

Anonymous — Thu, 10 Jul 2014 17:52:33 GMT

Thanks, the -= works fine for a "not equal" condition. Are there any other values to test for greater than or less than (other than the obvious > or < or >= or <=)? I would not of thought of using -= for not equal.

My dates are in date format. I pull them from a SQL script off an oracle database. I am using them in selecting records based on a Master Calendar that I set up for the application. All of that works fine.

Re: Using multiple parameters in an expression statement

rwunderlich — Thu, 10 Jul 2014 19:22:51 GMT

An subtle but important distinction here.

"-=" does not mean "not equal". It means "remove from the set".

"=-" (note order of chars) means "not equal" - or "equal to not".

So given a Dim1 set of A,B,C. Assume "B,C" are current selections.

<Dim1-={C}> returns the Set "B"

<Dim1=-{C}> returns the Set "A,B"

-Rob

Re: Using multiple parameters in an expression statement

Anonymous — Fri, 11 Jul 2014 14:47:30 GMT

Thanks for that clarification Rob.

I still need help with my original question. How do I represent an expression for counting the number of accounts with Opendate < 5/30/2014 (or any date)? I have verified that the fields I am testing are dates. I even went
as far as defining the Opendate field in my edit script as follows:

MAKEDATE(YEAR(OPENDATE),MONTH(OPENDATE),DAY(OPENDATE)) AS OPENDATE

If I use the following expression: SUM({1<OPENDATE ={'5/30/2014'}>}CTR_OPENED) I get a correct value for number of accounts opened on 5/30/2014.

When I change the expression to SUM({1<OPENDATE ={'< 5/30/2014'}>}CTR_OPENED) (as suggested above), I get a result of zero. Is this the proper syntax for evaluating a value with a "less than" condition?

Thanks all for your help with this.

Re: Using multiple parameters in an expression statement

Thu, 26 Feb 2015 14:44:48 GMT

Hi Alessandro,

Taking the opportunity I would like to ask for a similar issue. How could I write the ''or'' condition within an expression.

=sum({<condition={'text'} or condition2={'text2'})>} #values)

I know how to write it for an ''and'' (just adding a "," between both conditions).

Would appreciate any help!