topic Re: Issue with expression Count If Count in QlikView

Issue with expression Count If Count

Wed, 11 Oct 2017 07:22:53 GMT

Can someone please help me with this expression. New to qlikview and still getting to grips with the expression.

This is the expression I've come up with but its giving an error:

=Count(if([StartDate] > Today(), if(Count(REQ)=0,0)))

What I'm essentially trying to do it count the number of employees starting in the future and have no requests tied to them, so if theres 20 employees starting in the future and 15 don't have a request the count would be 15.

In SQL it would look something like this:

Select Count(*)

From Employee,Request

Where Employee.Id = Request.Id

AND StartDate > Today()

AND (Select REQ

FROM Request

GROUP By REQ

Having Count(REQ) = 0

)

Thanks for any help!

Re: Issue with expression Count If Count

Anil_Babu_Samineni — Wed, 11 Oct 2017 10:00:34 GMT

Try this?

Aggr(Count({<StartDate = {"=StartDate >= Today()"}, Employee.Id = {"=Employee.Id = Request.Id"}, REQ = {"=Count(REQ) = 0"}>} ), Req)

OR You can create flag for those and you can degrade the flag in the expression if needed..

Re: Issue with expression Count If Count

Kushal_Chawda — Wed, 11 Oct 2017 10:05:55 GMT

try this

=count(distinct {<StartDate={">$(=date(today(),'DD/MM/YYYY'))"}, Employee={"=Count(REQ)=0"}>}Employee)

Note: Please change your Date format according to StartDate format. You can simply link Employee and Request table in QlikView