5 Replies Latest reply on Nov 29, 2016 10:23 PM by Shinichiro Murakami

# How to create limit condition on aggregated value in filter card?

I am trying to limit my chart to show only the 6 most recent values based on week. However my week filter is an aggregated calculated field conditioned on year.

When i try to add a limit to the agg(week) filter (third filter in the box), the option is grayed out causing me to have to manually select the last 6 weeks.

The calculated field formula for the week field is:

if not ISNULL(attr(forecast]))

then attr([Fiscalweek])

END

The Fiscalweek field has 1-52 weeks but im only interested in the latest value i have which is currently at  week 15. It increments every week by 1. Hence, I needed to create the calculated field take out values that are null (in the future, such as week 16 onwards).

• ###### 1. Re: How to create limit condition on aggregated value in filter card?

Jenn

I want to understand your data structure.

Could you attach your packaged workbook?

Thanks,

Shin

• ###### 2. Re: How to create limit condition on aggregated value in filter card?

Attached: For this question, please refer to the second worksheet in the workbook.

• ###### 3. Re: How to create limit condition on aggregated value in filter card?

Hi Jenn,

You can create a calculated field as DATEADD('day',6, DATETRUNC('week',[Date Dimension])). When you add this field to the filter shelf, select Top 6 values based on the same field. Please refer below image regarding the filter condition.

Hope this helps.

Thanks,

Nachiket

1 of 1 people found this helpful
• ###### 4. Re: How to create limit condition on aggregated value in filter card?

Jenn

Pretty similar concept with another one.

[Filter last 6 weeks]

and

Show only "True" on filtering card.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 5. Re: How to create limit condition on aggregated value in filter card?

Sorry, should be "-5" instead of "-6"

[Filter last 6 weeks]