topic Re: Count if query in App Development

Count if query

richardm90 — Fri, 09 Feb 2018 00:37:00 GMT

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 — Fri, 09 Feb 2018 01:16:11 GMT

Try this may be -

count(

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

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

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

Digvijay_Singh — Fri, 09 Feb 2018 01:19:59 GMT

Or may be -

count(

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

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

richardm90 — Mon, 12 Feb 2018 00:50:48 GMT

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

richardm90 — Mon, 12 Feb 2018 01:23:12 GMT

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

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

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

Anonymous — Mon, 12 Feb 2018 03:41:15 GMT

you should check this to understand

sunny_talwar — Mon, 12 Feb 2018 12:44:24 GMT

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

richardm90 — Mon, 12 Feb 2018 22:58:05 GMT

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.