please help in customize data filtering

[anonymous008_G]

Hi – I am working on one of the automation project and below sample data already generated using custom filter tool which filters last 3-month date range using as of date column value. Now I want filter more this data such way that from last quarter end only input type id 7, 32 & 41 to be filtered along with records date ranging from 04/01/2024 to 06/30/2024. Rest everything to be filtered out.

How would I achieve this? Can someone please help.

Please refer below sample input table and expected output.

Input table:- 

acct_id	sec_id	input_type_id	input_date	input_value	As of Date
999	111	5	3/31/2024	900000	6/30/2024
999	111	11	3/31/2024	1000000	6/30/2024
999	111	13	3/31/2024	1100000	6/30/2024
999	111	20	3/31/2024	1200000	6/30/2024
999	111	36	3/31/2024	1300000	6/30/2024
999	111	7	3/31/2024	100000	6/30/2024
999	111	32	3/31/2024	200000	6/30/2024
999	111	41	3/31/2024	300000	6/30/2024
999	111	1	5/6/2024	400000	6/30/2024
999	111	6	6/13/2024	500000	6/30/2024
999	111	41	6/30/2024	600000	6/30/2024
999	111	7	6/30/2024	700000	6/30/2024
999	111	32	6/30/2024	800000	6/30/2024
999	111	5	6/30/2024	900000	6/30/2024
999	111	11	6/30/2024	1000000	6/30/2024
999	111	13	6/30/2024	1100000	6/30/2024
999	111	20	6/30/2024	1200000	6/30/2024
999	111	36	6/30/2024	1300000	6/30/2024

expected output:- 

acct_id	sec_id	input_type_id	input_date	input_value	As of Date
999	111	7	3/31/2024	100000	6/30/2024
999	111	32	3/31/2024	200000	6/30/2024
999	111	41	3/31/2024	300000	6/30/2024
999	111	1	5/6/2024	400000	6/30/2024
999	111	6	6/13/2024	500000	6/30/2024
999	111	41	6/30/2024	600000	6/30/2024
999	111	7	6/30/2024	700000	6/30/2024
999	111	32	6/30/2024	800000	6/30/2024
999	111	5	6/30/2024	900000	6/30/2024
999	111	11	6/30/2024	1000000	6/30/2024
999	111	13	6/30/2024	1100000	6/30/2024
999	111	20	6/30/2024	1200000	6/30/2024
999	111	36	6/30/2024	1300000	6/30/2024

[CoG]

Assuming the date fields are of the Date data type, you could use something like the following:

([input_date] >= "2024-04-01" AND

[input_date] <= "2024-06-30") OR

input_type_id in (7,32,41)

I'm not sure if this is what you were asking, so please provide more detail if not.

[anonymous008_G]

@CoG - thanks for the response but this is not i am looking for. I want date range to be filter based on as of date value. In above expression if as of date value changes then it wouldn't work. 

Currently I am using below expression to filter last 3 month data from input source and i want to filter further as per above requirement.  

DateTimeParse([input_date],'%y/%m/%d')

>=

DateTimeAdd(DateTimeParse([As of Date],'%y/%m/%d'),-3,'month')DateTimeParse([input_date],'%y/%m/%d')
>=
DateTimeAdd(DateTimeParse([As of Date],'%y/%m/%d'),-3,'month')

[anonymous008_G]

What I am expecting from above sample input data is :-

 I want to filter more such way that from last quarter end i.e (03/31/2024 in the case) input type id 7, 32 & 41 to be filtered and rest all the records date ranging from 04/01/2024 to 06/30/2024. Rest input type id of last quarter end to get filter out i.e 5,11,13,20 and 36.

[binuacs · Accepted solution]

@anonymous008_G 

[anonymous008_G]

This is great @binuacs. Thank you so much formula tool expressions. This will be helpful in future as well.