Filter out current week from analysis?

Question & Answer
1

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?

2

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 QuickSight 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 for a walk through of the functions.

3

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

image
image261×545 18.1 KB

4

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.

5

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

6

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 QuickSight Community Q&A Forum!