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 |
Solved! Go to Solution.
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.
@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.
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.
@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.
Hi,
I managed to get the desired output. appreciate your response on this. Thank you