Hi, Can someone please help me? how can i filter below table with condition where Date is month-end date for those records if MV is not equals to 0 and Capcon or Dist is equals to 0. only those records to come in the final table along with those non month-end date records. Input table:- DateCapConDistMV10/18/2024100000020000003/31/202500600000011/10/202180000001000000011/22/2021500000001400000012/17/202192000000180000005/4/20221340000002200000012/14/2022176000000260000002/5/2024218000000300000003/31/2025034000000340000003/31/20250038000000 Expected output :- DateCapConDistMV10/18/2024100000020000003/31/202500600000011/10/202180000001000000011/22/2021500000001400000012/17/202192000000180000005/4/20221340000002200000012/14/2022176000000260000002/5/2024218000000300000003/31/20250038000000

need help in conditional filtering.

Hi,

Can someone please help me? how can i filter below table with condition where Date is month-end date for those records if MV is not equals to 0 and Capcon or Dist is equals to 0. only those records to come in the final table along with those non month-end date records.

Input table:-

Date	CapCon	Dist	MV
10/18/2024	100000	0	2000000
3/31/2025	0	0	6000000
11/10/2021	800000	0	10000000
11/22/2021	5000000	0	14000000
12/17/2021	9200000	0	18000000
5/4/2022	13400000	0	22000000
12/14/2022	17600000	0	26000000
2/5/2024	21800000	0	30000000
3/31/2025	0	34000000	34000000
3/31/2025	0	0	38000000

Expected output :-

Date	CapCon	Dist	MV
10/18/2024	100000	0	2000000
3/31/2025	0	0	6000000
11/10/2021	800000	0	10000000
11/22/2021	5000000	0	14000000
12/17/2021	9200000	0	18000000
5/4/2022	13400000	0	22000000
12/14/2022	17600000	0	26000000
2/5/2024	21800000	0	30000000
3/31/2025	0	0	38000000

Help

Accepted answers

Qiu

@anonymous008_G
There is a formula " DateTimeTrim([Date_Out],"lastofmonth")" can easily get the last day of the Month, after we convert the "Date" column to DateTime format.

I was not fully understand the criteria about "and MV and CapCon, or Dist is not equals to 0. " and can not be sure about logic of And and Or.
So maybe you can change the formula in the Filter tool a bit.

0605-anonymous008_G.yxmd

All comments

Diederik_vanderharst

Hi! Try this:

If
[Date] = Datetimetrim([Date], "Lastofmonth")
and [MV] = 0
and ([CapCon] != 0 or
[Dist] != 0)
Then 'False'
Else 'True'
Endif

Maybe I misunderstood some of your rules, but I think the condition of the date was the most difficult, so I'm guessing you'll get there now.

anonymous008_G

@Diederik_vanderharst - Thanks for your response. but this is not working; maybe we need some different approach. Basically, I want to get rid of the records where Date is month-end and MV and CapCon, or Dist is not equals to 0.

anonymous008_G

If this is something confusing, can you please advise using formula tool how could i create new column which will flag month end date yes and non month end dates as no.

Qiu

@anonymous008_G
There is a formula " DateTimeTrim([Date_Out],"lastofmonth")" can easily get the last day of the Month, after we convert the "Date" column to DateTime format.

0605-anonymous008_G.yxmd

anonymous008_G

Hi,

I managed to get the desired output. appreciate your response on this. Thank you

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703