Filtering a column that exist in another column

Question

Hello,

I am given four columns: STOCK_ISSUER, YEAR, QUARTER, and VALUE. Is it possible to filter stock that exist in specific year and quarter, but not in other year and quarter?

For instance, I want to test stocks that are invested in 2020 Q1 but not in 2020 Q2 and Q3, how can I do so?

danilang · Answer

Hi @ringocheng618

To get a dynamic solution, you'll need more than just filters.

The formula tool builds a date for each quarter in the from 2020-01-01.  The top summarize finds the maximum overall date and the bottom one finds the max date for each Issuer.  Join these on Max_Date and look at the R output.   These will be the records that stops investing before the last quarter

Dan

Issuers.yxzp

ringocheng618 · Answer

Hello I have created a dummy data.

Obviously data would not be that small. But what I want to know is that, with that many stock issuers, how can I know which stock is not invested between 2020 Q2-Q4, but its invested prior to that.

For instance, Delta Air Lines in this data can be easily seen because we saw it appear prior to 2020 Q2, but just no longer here when it's 2020 Q2.

Thanks in advance!

DummyDataForFiltering.xlsx

skeen503979 · Answer

@ringocheng618

You can definitely accomplish that either using multiple filters (per column) or writing a custom filter.  Attached and below are how to configure the filter tool for what you're wanting.

Filter columns.yxmd