topic Using Greater Than in Set Analysis in QlikView

Using Greater Than in Set Analysis

tmumaw — Mon, 16 Apr 2012 13:30:31 GMT

I am trying to use the following : Count ({<CampCreateDate {">= [Document Date]"}>} DISTINCT Contract_ID), but keep getting an error. I want to count any contract where campaign create date is greater than or equal to document date.

Thanks
Thom

Using Greater Than in Set Analysis

etay_netintent — Mon, 16 Apr 2012 13:36:00 GMT

Set analysis only recognised '=' there fore you have to use ='<='

Look at this example from the help

Sum({<Year = {">=1978"}> } Sales)

Etay

Re: Using Greater Than in Set Analysis

etay_netintent — Mon, 16 Apr 2012 13:40:29 GMT

for starters Try CampCreateDate = {

Which version are you working with ?? in addition to adding the = Set analysis had a problem comparing dates. We turn the date into a number and compare on that.

Etay

Using Greater Than in Set Analysis

CELAMBARASAN — Mon, 16 Apr 2012 13:40:57 GMT

Hi,

Check with this

Count ({<CampCreateDate {"=Aggr(If(CampCreateDate >=[Document Date],CampCreateDate), CampCreateDate)"}>}DISTINCT Contract_ID)

Celambarasan

Re: Using Greater Than in Set Analysis

tmumaw — Mon, 16 Apr 2012 13:46:11 GMT

Set modifier error

Re: Using Greater Than in Set Analysis

tmumaw — Mon, 16 Apr 2012 13:58:05 GMT

We are running 10 SR 4

Using Greater Than in Set Analysis

CELAMBARASAN — Mon, 16 Apr 2012 14:02:06 GMT

Hi,

typo mistake

Count ({<CampCreateDate ={"=Aggr(If(CampCreateDate >=[Document Date],CampCreateDate), CampCreateDate)"}>}DISTINCT Contract_ID)

Or check with this also

Count ({<CampCreateDate ={"=Aggr(If(CampCreateDate >=[Document Date],CampCreateDate), CampCreateDate,[Document Date])"}>}DISTINCT Contract_ID)

Celambarasan

Using Greater Than in Set Analysis

danielrozental — Mon, 16 Apr 2012 14:26:32 GMT

Sorry but set analysis won't work for that. Reason is it's not processed as row per row basis, the set analysis result set is one and gets processed before the object does.

You need to do something like this or you can just add a flag in the script for those documents

Count (DISTINCT if(CampCreateDate >= [Document Date],Contract_ID))

Re: Using Greater Than in Set Analysis

Thu, 19 Jun 2014 08:51:47 GMT

Hi Thom!!

Are you still interested for solution?

I didn't recognize the post date!!!

However this expression works properly:

Supposing your pivot table has a structure like this:

Contract_ID - CampCreateDate - Document Date

the right expression is:

count ({< CampCreateDate= {'>=$(=date(max(aggr(max([Document Date]),Contract_ID&CampCreateDate&[Document Date])),'DD/MM/YYYY'))'} >} DISTINCT Contract_ID)

If you have problem I can send you my sample Qvw!

Let me know...

Bye!!

Re: Using Greater Than in Set Analysis

SunilChauhan — Thu, 19 Jun 2014 09:00:37 GMT

simply use below code

Count ({<CampCreateDate= {">=$(= [Document Date])"}>} DISTINCT Contract_ID)

hope this helps

Re: Using Greater Than in Set Analysis

alec1982 — Thu, 25 Sep 2014 03:48:45 GMT

Hi guys,

the following made it for me using Min and Max

num(sum( {$<RDATEValue={">=$(=(AddMonths(min([RDATEValue]),-12))) <=$(=(AddMonths(max([RDATEValue]),-12)))"}>} REV))

but what if the user makes few selections on the RDATEValue field and they are not in order?

meaning what if the user select 09/01/2014 and 06/01/2014 and 01/01/2014 We need to get the REV for the same periods from the previous year..

Let me know your thoughts..

Re: Using Greater Than in Set Analysis

rajendra1918 — Tue, 20 Jun 2017 07:15:23 GMT

Hi All,

From the duration i'm trying to find greater than 95 days, 45 days and 30 days count on unique column Can you please help me how can i achieve it.

Re: Using Greater Than in Set Analysis

Kushal_Chawda — Tue, 20 Jun 2017 07:57:37 GMT

try this

Count ({<CampCreateDate= {"=CampCreateDate >= [Document Date]"}>} DISTINCT Contract_ID)

Re: Using Greater Than in Set Analysis

rajendra1918 — Tue, 20 Jun 2017 09:22:33 GMT

Hi Kushal- Thanks for your quick response.

Count ({<CampCreateDate= {"=CampCreateDate >= [Document Date]"}>} DISTINCT Contract_ID)

i have a confusion with below columns only. can u please specify on this. which i have to use.

CampCreateDate

[Document Date]

contract ID i know as i have contract number.

Requirement --> count of >90 days,>45 days & >35 days .

Regards,

Rajendra

Re: Using Greater Than in Set Analysis

campbellr — Wed, 16 Aug 2017 04:14:21 GMT

I know this was a few years ago now but I've just found a need and this sorts it for me. Thanks

Re: Using Greater Than in Set Analysis

akshaye_c_navale — Wed, 16 Aug 2017 08:08:03 GMT

Try This,

Calculate difference between dates for getting no of days says NoOfDays

say vDaysCondition1=90

,vDaysCondition2=45

,vDaysCondition3=35

for count greater than 90 -->> Count({<[NoOfDays] = {'>=$(vDaysCondition1)'>}DISTINCT Contract_ID)

for count greater than 45 -->> Count({<[NoOfDays] = {'>=$(vDaysCondition2)<$(vDaysCondition1)'>}DISTINCT Contract_ID)

for count greater than 35 -->> Count({<[NoOfDays] = {'>=$(vDaysCondition3)<$(vDaysCondition2)'>}DISTINCT Contract_ID)