topic Re: Help with set analysis syntax in App Development

Help with set analysis syntax

mrbustin — Thu, 08 Feb 2018 14:20:00 GMT

Greetings,

I need to filter actions that meet a few conditions, and I'm stuck on the syntax to test for the result of a Networkdays() function that evaluates to less than or equal 20 days. I just cannot figure out the correct syntax, can someone please take a look?

My formula is:

Count({$<FOIAType={'Request'},Status ={'Closed'},ClosedDate = {">=10/01/2017"}, NetWorkDays(DateReceived,ClosedDate,$(vHolidays)) = {"<=" 20} >}TrackingNum)

Thanks,

Mark

Re: Help with set analysis syntax

sunny_talwar — Thu, 08 Feb 2018 17:22:43 GMT

May be try this

Count({$<FOIAType={'Request'}, Status ={'Closed'}, ClosedDate = {">=10/01/2017"}, TrackingNum = {"=NetWorkDays(DateReceived, ClosedDate, $(vHolidays)) <= 20"}>} TrackingNum)

Re: Help with set analysis syntax

aschmeelk — Thu, 08 Feb 2018 17:52:58 GMT

Mark,

Are you getting an error or just no data?

Re: Help with set analysis syntax

mrbustin — Tue, 13 Feb 2018 17:44:44 GMT

Hi Raymond,

Getting no data ... can

this syntax is sooo confusing ...

The if statement syntax is easy enough for a table:

If(FOIAType='Request' and Status = 'Closed' and ClosedDate >= '10/01/2017' and NetWorkDays(DateReceived,ClosedDate,$(vHolidays)) <=20,FYReceived,null())

but trying to put this in a set analysis formula to generate a count of the records seems near impossible ...

-mark

Re: Help with set analysis syntax

mrbustin — Tue, 13 Feb 2018 18:02:18 GMT

Sunny, this seems to work! Thank you!!! but I don't understand why it works ... it looks like your comparing a text field(trackingnum) to a numerical test '<=20' but it gives me 134 records and the data appears to be correct.

My regular table using the below formula gives me 135 - a set the received date to null and only display non nulls ...

=If(FOIAType='Request' and Status = 'Closed' and ClosedDate >= '10/01/2017' and NetWorkDays(DateReceived,ClosedDate,$(vHolidays)) <=20,FYReceived,null())

Thank you!

Re: Help with set analysis syntax

sunny_talwar — Tue, 13 Feb 2018 18:10:00 GMT

I am not comparing trackingnum to a numerical test. What the expression is doing is picking all those trackingnums where the set analysis condition is true. So, =NetWorkDays(DateReceived, ClosedDate, $(vHolidays)) <= 20 is evalulated as a Boolean condition where Trackingnum is a dimension and all those places where it is true are included in the expression.

Does that make sense?

Re: Help with set analysis syntax

mrbustin — Tue, 13 Feb 2018 18:12:57 GMT

It's beginning to ... thank you again. -Mark