7 Replies Latest reply on Dec 7, 2016 10:50 AM by Shinichiro Murakami

# Filter by Last Common Date

Hi,

I want to filter my data by the last common date - so MAX the month/year that each country had any data (February) but I am struggling to get the calculation.

Please see the attached workbook, I am looking to filter Sheet 2 by February 2016, but to have this dynamic so it takes the month where all three countries have any data/no blanks.

Let me know if this doesn't make any sense.

• ###### 1. Re: Filter by Last Common Date

Hi Eleonor

This may work?

[Filter]

attr({fixed [Date]:countd([Country])})=attr({fixed:countd([Country])})

[Filter index]

(if [Filter] then last() end )= 0

Both filter only "True", then only February is shown.

Thanks,

Shin

• ###### 2. Re: Filter by Last Common Date

Hi Eleonor

Find my approach as reference below and stored in attached workbook version 9.3

a. maxdate: {fixed [Country]:max([Date])}\

b. if attr([max([Date]])])=attr([Date]) then sum([Value]) END

• ###### 3. Re: Filter by Last Common Date

Thank you for your replies Shin and Norbert Maijoor but I am looking to have the previous months data as well.

So simply put drag the date to filter and choose Ending Date: February 2016 but have it dynamic so it filters by the last date where all the countries have data. Make sense?

Thank you once again.

• ###### 4. Re: Filter by Last Common Date

Change the formula to show the last two months of full data set.

[Filter index]

(if [Filter] then last() end )= 0 or (if [Filter] then last() end )= 1

Thanks,

Shin

• ###### 5. Re: Filter by Last Common Date

Thanks again for the reply Shin!

My only struggle now is that it limits it to the past two months, if I add in more dates it will only display the last two months. The workbook I added was just a dummy, my real data has two years of data. Apologies if I am missing something obvious!

• ###### 6. Re: Filter by Last Common Date

Hi Eleonor,

Find my "second attempt";) as reference below and once again stored in attached workbook version 9.3

• ###### 7. Re: Filter by Last Common Date

I'm bit confused, but remove this filter to show all months with full set..

[Filter index]

(if [Filter] then last() end )= 0 or (if [Filter] then last() end )= 1

Thanks,

Shin