5 Replies Latest reply on May 18, 2017 9:05 AM by Okechukwu Ossai

# Apply Filter to Future Dates Only?

Hello,

I've attached a sample workbook with data for what I'm trying to accomplish.

I have counts of patients by country by month. I'd like to exclude a country (filter on the right) so that only counts in future months (Date >= TODAY()) are affected. Therefore past months count of patients shouldn't be changing at all. I've tried adding a condition to the filter but it doesn't affect the counts at all.

Thank you,

Tanya

• ###### 1. Re: Apply Filter to Future Dates Only?

Hi Tanya

This is not perfect because you still can see "Always keep" in addition to country list, but may be good enough.

Thanks,

Shin

• ###### 2. Re: Apply Filter to Future Dates Only?

Hi Tanya

see the attached

Not certain I have what you want

The formula became complex

IF  attr(MONTH([Month])) <= attr(MONTH(today()))

then sum({ FIXED  [Month]:((sum([Patients])))})

else sum(({ FIXED([Month]), [Country] : sum([Patients])})) end

Let me know if this helped

Jim

• ###### 3. Re: Apply Filter to Future Dates Only?

Hi Tanya and Jim,

I have the same approach as Jim. However,  that solution will make the count of both current month and past months to remain unchanged.

So for past months to remain unchanged and only future months (including current month) to be affected, you will need to replace the <= in Jim's formula with <.

Otherwise you can use the alternative formula below.

IF ATTR([Month]) >= DATE(DATETRUNC('month', TODAY())) THEN SUM({FIXED [Month], [Country]: SUM([Patients])})

ELSE SUM({FIXED [Month]: SUM([Patients])})

END

The formula above assumes that your Date will always be in the first day of the Month format used in the sample data.

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 4. Re: Apply Filter to Future Dates Only?

Thank you all so much! I used Ossai's solution but they were all helpful.

• ###### 5. Re: Apply Filter to Future Dates Only?

You're welcome Tanya. Glad it helped.