need help in conditional filtering.

[anonymous008_G]

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

[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 · Accepted solution]

@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. 

[anonymous008_G]

Hi,

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