5 Replies Latest reply on Aug 9, 2018 2:43 PM by Shinichiro Murakami

# Filtering by X occurrences in non-continuous distinct dates

In the Sheet 2 worksheet, I'm attempting to only display the students that have attended 2 or more activities in the most recent 4 dates.

I've tried making a Fixed statement that would count the names to attempt to drop the formula into filter to say anything >= 2 to display but I haven't been able to make this work.

I'm not sure if it's possible to do parameters that would say Any X amount of participation through X date ranges (not just the most recent 4 dates).

The hope is to be able to do this filtering so I can build the next step, which would be the amount of time done in each activity overall by those that do 2 or more activities.

Any help would be appreciated, either from sources I should read/watch, other examples that have done this, etc.  I haven't been able to solve this in the past few days.

Thanks.

• ###### 1. Re: Filtering by X occurrences in non-continuous distinct dates

HI Juan,

Not exactly sure which count you expect regarding name x teacher, anyways assuming "counting NAME" as goal.

One of the keys is judging "last 4".

We can use "table calculation" nit it brings may complexities.

Here, assuming the data size is not significantly huge, used nested LOD to make calculations easy.

(4 or 5 may be limit for this method, 10 is too many where you need to use tale calc.)

Thanks,

Shin

• ###### 2. Re: Filtering by X occurrences in non-continuous distinct dates

Hi Shin,

You are correct, the goal is the count of names

I have a few questions.

With the Last 4 Dates, does that mean I no longer need the Date filter I have set on the sheet?

What was the reasoning of using a formula to filter the date instead of dropping date in the filter?

Shinichiro Murakami wrote:

One of the keys is judging "last 4".

We can use "table calculation" nit it brings may complexities.

Here, assuming the data size is not significantly huge, used nested LOD to make calculations easy.

(4 or 5 may be limit for this method, 10 is too many where you need to use tale calc.)

Later I will have to deal with a file similar to this that will be much larger, could you show me the other method that is not limited to 4 or 5?

Thank you,

Juan

• ###### 3. Re: Filtering by X occurrences in non-continuous distinct dates

Yes, in case you only want to see last 4 "dates" point,

You can change date filter to context to limit LOD cal overall.

Filters and Level of Detail Expressions

As a results, the calculation becomes much simpler.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Filtering by X occurrences in non-continuous distinct dates

Hi Shin,

Thank you so much for you help so far.

If I wanted the date range to be 6/1 to 6/11, would I have to do Last 10 date, with a count activity formula such as:

{FIXED  [Name]: COUNTD(if [Date]>=[Last 10 Dates] and [Date]<=[Last 6 Dates] then [Activity] END)}

or is it better to do a date filter with context?

Thanks,

Juan

• ###### 5. Re: Filtering by X occurrences in non-continuous distinct dates

Hi Juan,

Filter with context is the easiest.

and if the solution works, please mark my answer as correct to close the thread, not from inbox but from original post.

Thanks,

Shin