Help with set analysis syntax

[mrbustin]

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

[sunny_talwar]

May be try this

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

[aschmeelk]

Mark,

Are you getting an error or just no data?

[mrbustin]

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

[mrbustin]

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!

[sunny_talwar]

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?

[mrbustin]

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