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_idsec_idinput_type_idinput_dateinput_valueAs of Date99911153/31/20249000006/30/2024999111113/31/202410000006/30/2024999111133/31/202411000006/30/2024999111203/31/202412000006/30/2024999111363/31/202413000006/30/202499911173/31/20241000006/30/2024999111323/31/20242000006/30/2024999111413/31/20243000006/30/202499911115/6/20244000006/30/202499911166/13/20245000006/30/2024999111416/30/20246000006/30/202499911176/30/20247000006/30/2024999111326/30/20248000006/30/202499911156/30/20249000006/30/2024999111116/30/202410000006/30/2024999111136/30/202411000006/30/2024999111206/30/202412000006/30/2024999111366/30/202413000006/30/2024 expected output:- acct_idsec_idinput_type_idinput_dateinput_valueAs of Date99911173/31/20241000006/30/2024999111323/31/20242000006/30/2024999111413/31/20243000006/30/202499911115/6/20244000006/30/202499911166/13/20245000006/30/2024999111416/30/20246000006/30/202499911176/30/20247000006/30/2024999111326/30/20248000006/30/202499911156/30/20249000006/30/2024999111116/30/202410000006/30/2024999111136/30/202411000006/30/2024999111206/30/202412000006/30/2024999111366/30/202413000006/30/2024

please help in customize data filtering

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

Help

Accepted answers

binu_acs

@anonymous008_G

LastQ_10102024.yxmd

All comments

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703