3 Replies Latest reply on Nov 7, 2016 1:55 PM by Shinichiro Murakami

    Exclude observations if missing data (nulls) over time range specified by date filter?

    luciana.suran.1

      Hi,

       

      I have some monthly time series data which a bunch of accounts (Acct ID). The first data-point for each account is either 1/31/1982 or 1/31/2013.

       

      I'd like to specify a date range using a date filter - in the attached workbook the filter is currently set for "Last 8 years" (1/1/2009 to 12/31/2016). Here's my question - is there any way I can exclude those Accounts that have missing data during that time range? In this specific case I would want all the Accounts with an ID < 301 to be excluded since they do not gave a value for each month in the date range.

       

      I can get around this using parameters but I'm wondering if it's possible using a Date filter. In the past I have solved for such cases using ZN(LOOKUP(etc.)) but it appears I cannot use this with dates.

       

      Would appreciate any advice! Thanks!