Count if query

[richardm90]

Hi Guys,

I'm currently creating a KPI to show a total number of records that have a Creation Date and a Start Date

i.e. count(NetWorkDays("Creation Date", "Start Date"))

However, I only want to include records where there is no value in the cancelled column (i.e. the appointment has not been cancelled)

I also want to exclude records that are follow up appointments , these have a "Y" in the follow ups column....

So I tried this

count(

{<[Cancelled]={"NULL"}>}

{<[Followup]-={"Y"}>}

NetWorkDays("Creation Date", "Start Date"))

But this does not work. There is also a possibility that an appointment could be Cancelled AND be a follow up.

Any help here would be much appreciated.

[Digvijay_Singh]

Try this may be -

count(

{<[Cancelled]-={"*"}>}

{<[Followup]-={"Y"}>}

NetWorkDays("Creation Date", "Start Date"))

[Digvijay_Singh]

Or may be -

count(

{<[Cancelled]={"=Len(Cancelled) = 0"},[Followup]-={"Y"}>}

NetWorkDays("Creation Date", "Start Date"))

[richardm90]

Thank you very much for your help on this one Digvijay.

[richardm90]

HI Digvijay,

I'm still having issues representing NULL, {"=Len(Cancelled) = 0"

and {<[Cancelled]-={"*"}>}

don't seem to work. Any further pointers would be great.

[Anonymous]

you should check this to understand

Set analysis and set expressions ‒ Qlik Sense

[sunny_talwar]

Would you be able to share few rows of data and expected output from this data?

[richardm90]

Thanks a lot for the help guys. i'll check out the set analysis and set expressions and then send over some sample data if I'm still stuck.