Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be try this
Count({$<FOIAType={'Request'}, Status ={'Closed'}, ClosedDate = {">=10/01/2017"}, TrackingNum = {"=NetWorkDays(DateReceived, ClosedDate, $(vHolidays)) <= 20"}>} TrackingNum)
Mark,
Are you getting an error or just no data?
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
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!
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?
It's beginning to ... thank you again. -Mark