Hi – I want to filter last 3 months of date range from input date column using As of Date column value. How would I do that in filter tool. I am not able to write the correct formula. Please. help me in this. In the expected output if my as of date is 06/30/2024 then I am expecting data to be filter from March month end to Jun month end i.e 03/31/2024 to 06/30/2024. Input data:- acct_idinput_dateinput_valueAs of Date1005/28/2021-50000008.26/30/20241007/22/2021-50000006/30/20241008/11/2021-385000006/30/20241003/31/2024375000006/30/20241004/10/202410000006/30/20241004/22/2024-10000006/30/20241004/30/202410000006/30/20241005/2/2024-10000006/30/20241005/15/20249800006/30/20241005/25/2024-72999006/30/20241006/30/202473001006/30/20241006/30/202472199086/30/20241006/30/2024-73001006/30/20241006/30/202355100000.26/30/20241006/30/2023551000006/30/20241006/30/2023-0.096/30/2024 Expected Output:- acct_idinput_dateinput_valueAs of Date1003/31/2024375000006/30/20241004/10/202410000006/30/20241004/22/2024-10000006/30/20241004/30/202410000006/30/20241005/2/2024-10000006/30/20241005/15/20249800006/30/20241005/25/2024-72999006/30/20241006/30/202473001006/30/20241006/30/202472199086/30/20241006/30/2024-73001006/30/2024

Need to calculate & filter Date range

Hi – I want to filter last 3 months of date range from input date column using As of Date column value. How would I do that in filter tool. I am not able to write the correct formula. Please. help me in this.

In the expected output if my as of date is 06/30/2024 then I am expecting data to be filter from March month end to Jun month end i.e 03/31/2024 to 06/30/2024.

Input data:-

acct_id	input_date	input_value	As of Date
100	5/28/2021	-50000008.2	6/30/2024
100	7/22/2021	-5000000	6/30/2024
100	8/11/2021	-38500000	6/30/2024
100	3/31/2024	37500000	6/30/2024
100	4/10/2024	1000000	6/30/2024
100	4/22/2024	-1000000	6/30/2024
100	4/30/2024	1000000	6/30/2024
100	5/2/2024	-1000000	6/30/2024
100	5/15/2024	980000	6/30/2024
100	5/25/2024	-7299900	6/30/2024
100	6/30/2024	7300100	6/30/2024
100	6/30/2024	7219908	6/30/2024
100	6/30/2024	-7300100	6/30/2024
100	6/30/2023	55100000.2	6/30/2024
100	6/30/2023	55100000	6/30/2024
100	6/30/2023	-0.09	6/30/2024

Expected Output:-

acct_id	input_date	input_value	As of Date
100	3/31/2024	37500000	6/30/2024
100	4/10/2024	1000000	6/30/2024
100	4/22/2024	-1000000	6/30/2024
100	4/30/2024	1000000	6/30/2024
100	5/2/2024	-1000000	6/30/2024
100	5/15/2024	980000	6/30/2024
100	5/25/2024	-7299900	6/30/2024
100	6/30/2024	7300100	6/30/2024
100	6/30/2024	7219908	6/30/2024
100	6/30/2024	-7300100	6/30/2024

Help

Accepted answers

DataNath

Hey @anonymous008_G, you'll want to use something along the following lines. If you don't want it to be inclusive then just remove the '=' so that it's purely '>':

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

DataNath

Ah ok @anonymous008_G, I see you're going for quarter ends now. In that case I think the following ought to work:

[input_type_id]=41
&&
DateTimeParse([input_date],'%m/%d/%Y')
=
ToDate(DateTimeTrim(DateTimeAdd(DateTimeParse([As of Date],'%m/%d/%Y'),-3,'month'),'lastofmonth'))

All comments

DataNath

Hey @anonymous008_G, you'll want to use something along the following lines. If you don't want it to be inclusive then just remove the '=' so that it's purely '>':

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

anonymous008_G

@DataNath -- works as expected. Thanks a lot

anonymous008_G

Hi @DataNath – Also one more help needed regarding the same topic. I need to filter the data with one more condition that from the last 3-month range data I need to filter it with condition as wherever input code =41 and input date should -3-month end date of as of date. In this case this should be 03/31/2024.

I tried this syntax but output generating with 03/31/2024 and 06/31/2024. I need only 03/31 date in my final table.

[input_type_id]=41 OR (DateTimeParse([input_date],'%y/%m/%d')

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

DataNath

Hey @anonymous008_G, without seeing your exact data (the example provided above doesn't have an [input_type_id] field), it sounds like you'll want to add this to the expression with an AND operand. Something like:

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

anonymous008_G

Hi @DataNath - Apologies, i though now sample data is not needed. Below is sample data after using above custom filter which is filtering last 3 months data using as of date column. now i want to use one more custom filter and condition would be input_type_id is 41 and date would last quarter-end date of as of date column value. since as of date is 06/30/2024 then our code should filter for 03/31/2024. this is my requirement.

acct_id	input_type_id	input_date	input_value	As of Date
1000	11	3/31/2024	2000000	6/30/2024
1000	5	3/31/2024	300000	6/30/2024
1000	20	4/18/2024	5500000	6/30/2024
1000	13	3/31/2024	65498	6/30/2024
1000	41	3/31/2024	1815498	6/30/2024
1000	11	6/15/2024	1755147.4	6/30/2024
1000	5	5/30/2024	1694796.8	6/30/2024
1000	20	6/30/2024	1634446.2	6/30/2024
1000	13	6/30/2024	1574095.6	6/30/2024
1000	41	6/30/2024	1513745	6/30/2024
1000	11	3/31/2024	1453394.4	6/30/2024
1000	5	3/31/2024	1393043.8	6/30/2024
1000	20	3/31/2024	1332693.2	6/30/2024
1000	13	3/31/2024	1272342.6	6/30/2024
1000	41	4/22/2024	1211992	6/30/2024
1000	11	6/30/2024	1151641.4	6/30/2024
1000	5	6/30/2024	1091290.8	6/30/2024
1000	20	6/30/2024	1030940.2	6/30/2024
1000	13	6/30/2024	970589.6	6/30/2024
1000	41	5/22/2024	910239	6/30/2024
1000	32	3/31/2024	849888.4	6/30/2024

DataNath

Ah ok @anonymous008_G, I see you're going for quarter ends now. In that case I think the following ought to work:

[input_type_id]=41
&&
DateTimeParse([input_date],'%m/%d/%Y')
=
ToDate(DateTimeTrim(DateTimeAdd(DateTimeParse([As of Date],'%m/%d/%Y'),-3,'month'),'lastofmonth'))

anonymous008_G

@DataNath - Awesome, thanks a ton.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703