# Filter out current week from analysis?

**URL:** <https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241>\
**Category:** Q&A\
**Tags:** filters, quick-sight\
**Created:** [June 16, 2022, 9:13pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241 "2022-06-16T21:13:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://community.amazonquick.com/u/dturner)\
**Post date:** [June 16, 2022, 9:13pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241/1 "2022-06-16T21:13:18Z")

</div>

I need to show analysis through the last complete fiscal week of the year, ignoring everything from the partial current week.

Is is possible to construct a filter that ignores the Max Fiscal\_Week value?

---

<div class="post-metadata">

**Author:** ![robkc](https://avatars.discourse-cdn.com/v4/letter/r/8e7dd6/32.png) [@robkc](https://community.amazonquick.com/u/robkc)\
**Post date:** [June 20, 2022, 6:22pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241/2 "2022-06-20T18:22:26Z")

</div>

Hi dturner,

To do this you could create a new calculated field to filter out values from the latest week:

`ifelse({your date field} < truncDate('WK',maxOver({your date field}, [], PRE_AGG)), {your measure field}, 0)`

This breaks down as follows:

1. `maxOver` - finds the latest date in your data set - `PRE_AGG` is used as you can’t mix aggregated and none aggregated functions in a calculated field
2. `truncdate` - truncates this to the start of the week (Note: in Quick Sight the start of the week is Sunday)
3. The `ifelse` acts as a filter to remove any values in the latest week

At the start of this year we released a range of of period over period and period to date calculations - depending on your use case some of these might also prove useful. Check out [this blog](https://aws.amazon.com/blogs/big-data/add-comparative-and-cumulative-date-time-calculations-in-amazon-quicksight/) for a walk through of the functions.

---

<div class="post-metadata">

**Author:** ![Andy\_Brand](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andy_brand/32/368_2.png) [@Andy\_Brand](https://community.amazonquick.com/u/Andy_Brand)\
**Post date:** [June 20, 2022, 7:28pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241/3 "2022-06-20T19:28:22Z")

</div>

You can also so this with a time based filter that excluded the current week.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/b/b355ee8758caa8131a99cda32f13849a49c8635d.png)

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://community.amazonquick.com/u/dturner)\
**Post date:** [June 20, 2022, 7:57pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241/4 "2022-06-20T19:57:50Z")

</div>

Sadly, the Relative Date filter won’t work for me since I need to filter by the company’s Fiscal Week which does not correlate with calendar week.

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://community.amazonquick.com/u/dturner)\
**Post date:** [June 20, 2022, 9:15pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241/5 "2022-06-20T21:15:51Z")

</div>

Thanks for this info. I’ll give this a shot.

---

<div class="post-metadata">

**Author:** ![WLS-P](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-p/32/36109_2.png) [@WLS-P](https://community.amazonquick.com/u/WLS-P)\
**Post date:** [September 20, 2022, 4:16pm UTC](https://community.amazonquick.com/t/filter-out-current-week-from-analysis/4241/6 "2022-09-20T16:16:59Z")

</div>

Hi, @dturner. Did @robkc’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community Q&A Forum!