topic Re: Count Mathematical Expressions in QlikView

Count Mathematical Expressions

Anonymous — Thu, 19 Nov 2015 15:47:27 GMT

Hello,

I am trying to add a condition to my count statement in order to factor in if an amount is in between two others. The conditions go as this:

1. The CERTIFICATION_EXPIRATION DATE must NOT be null or zero.

2. The CERTIFICATION_EXPIRATION DATE minus vToday(Today's date) must be greater than or equal to 1 and less than or equal to 30.

If these conditions are met, RTC_TITLE is to be counted. This is what I have so far with no luck:

count( {$<CERTIFICATION_EXPIRATION_DATE -= {''}, CERTIFICATION_EXPIRATION_DATE -= {0} , CERTIFICATION_EXPIRATION_DATE-vToday = {"<=$(=(30))", ">=$(=(1))"}>} RTC_TITLE)

Any help is appreciated!

Thanks!

Re: Count Mathematical Expressions

sunny_talwar — Thu, 19 Nov 2015 15:57:42 GMT

I believe your third condition should take care of the first two:

=Count({$<CERTIFICATION_EXPIRATION_DATE = {"=(CERTIFICATION_EXPIRATION_DATE - vToday) <=30 and (CERTIFICATION_EXPIRATION_DATE - vToday) >=1"}>} RTC_TITLE)

Re: Count Mathematical Expressions

swuehl — Thu, 19 Nov 2015 16:01:51 GMT

You should use only a single set modifier for CERTIFICATION_EXPIRATION_DATE. I think, if 2. is met, also 1. is met.

Only field names left of the equal sign, no expressions (which need to go into the advanced search):

count( {$<CERTIFICATION_EXPIRATION_DATE = {">=$(=Date(Today()+1))<=$(=Date(Today()+30))"}>} RTC_TITLE)

You may need to add a date() format code to match the format of CERTIFICATION_EXPIRATION_DATE

Re: Count Mathematical Expressions

Anonymous — Thu, 19 Nov 2015 16:02:58 GMT

This is wonderful and works great as always Sunny! I'm just trying to feed too much in the equation and overthinking it.

Appreciate the help as always!

-Jordan