Custom Filter - Day of Month

[JCTAN]

Hi,

i have the below formulas in Custom Filter, basically i wanted it to filter the attached file for Broker Id column with just "CITI", "UBSWG", "UBSWU", if the day of the month is <=23, ELSE filter for "BOFA" AND "CHAS" AND "GSIL" AND "MSLNG". But the results came back all in FALSE.

IF DateTimeDay(DateTimeToday()) <= 23 THEN [Broker Id] = "CITI" AND "UBSWG" AND "UBSWU"
ELSE [Broker Id] = "BOFA" AND "CHAS" AND "GSIL" AND "MSLNG"
ENDIF

[davidskaife · Accepted solution]

Hi @JCTAN 

Try this in a Formula tool with a new column and datatype set as Bool, then filter on the result:

IF DateTimeDay(DateTimeToday()) <= 23 AND [Broker Id] IN ("CITI","UBSWG","UBSWU")
THEN 1
ELSE 0
ENDIF

  Alternatively try this directly in a Filter tool:

DateTimeDay(DateTimeToday()) <= 23 AND [Broker Id] IN ("CITI","UBSWG","UBSWU")

[JCTAN]

Thanks @davidskaife, how should i go about getting an output with [Broker Id] IN ("CITI","UBSWG","UBSWU")  IF the day of month today <= 23 

and getting an output with [Broker Id] IN "BOFA" AND "CHAS" AND "GSIL" AND "MSLNG" IF the day of month today >= 26

[davidskaife]

Hi @JCTAN 

If i understand correctly you want CITI, UBSWG or UBSWU if the day is <= 23 and if the day is >= 26 you want BOFA, CHAS, GSIL, and MSLNG correct?

What do you want if the day is 24 or 25, everything or nothing?

[JCTAN]

@davidskaife my process is i would run the workflow on 23rd and 26th of the month, If i run on 23rd of the month, i am expecting to see an output with just the details for CITI, UBSWG or UBSWU and if i run on 26th of the month, i would be seeing an output with the details for BOFA, CHAS, GSIL, and MSLNG.

[davidskaife · Accepted solution]

Hi @JCTAN 

In that case use this:

(DateTimeDay(DateTimeToday()) <= 23 AND [Broker Id] IN ("CITI","UBSWG","UBSWU"))
OR
(DateTimeDay(DateTimeToday()) >= 26 AND [Broker Id] IN ("BOFA","CHAS","GSIL","MSLNG"))

If it runs on the 24th or 25th you'll get no return at all.

Side note, if you only run it on the 23rd or the 26th then instead of <= or >= just change them to = so that way it only returns a result on those days

[JCTAN]

works perfectly! thank you @davidskaife