7 Replies Latest reply on Jun 9, 2017 7:30 AM by Rahul Upadhye

# Excluding the current incomplete week from Year over Year calculations

Hello,

I'm trying to figure out a way to exclude the current incomplete week from my calculations. The data source I'm connected to is bringing the week # is as a string so all of the out of the box date calculations that Tableau comes with are not available to me. Essentially the problem is that if I'm creating a YOY calculation it automatically will include the current week even if its incomplete. I've tried creating a Set that only includes weeks in which we have sales for and then filtering on the Max week # but Tableau wont let me do that. I feel like there has to be a way to exclude that current week because im fairly close but just not all the way there.

Any help would be greatly appreciated.

• ###### 1. Re: Excluding the current incomplete week from Year over Year calculations

how would you define incomplete?

those rows are absent? or those rows are Nulls ?

1 of 1 people found this helpful
• ###### 2. Re: Excluding the current incomplete week from Year over Year calculations

So lets say we are in our 10th fiscal week. Weeks 1-9 are complete so when I run a Year to date/ Year over Year calculation it gives me an accurate results because i'm comparing full weeks 1-9 from the current year against full weeks 1-9 from the previous year. But as soon as we enter into a new week the calculation is no longer accurate because I'm now comparing a partially completed week 10 from the current year with a fully complete week 10 from the previous year.

So I need a calculation that will dynamically exclude the current week without doing it manually.

Hopefully that makes sense.

• ###### 3. Re: Excluding the current incomplete week from Year over Year calculations

Hi,

How are you identifying when a week is complete? Once you have a calculated field for determining whether or not a week is complete, you could try a custom formula in the set operation (the set being your customer id, order id, whatever is your unique identifier). Max(IF [Week Sales Complete]='Complete' then 1 else 0 end)=1.

This video is super helpful, especially with dynamic sets.  Check the 8:30 mark.

• ###### 4. Re: Excluding the current incomplete week from Year over Year calculations

This is where the challenge is. Because the dashboard is going to update every day and we dont actually have a "day" field in the data source only week # there is nothing I can really use as an identifier other than when sales information starts to populate the next week.

• ###### 5. Re: Excluding the current incomplete week from Year over Year calculations

Sorry, ignore my last response (with this new information). Honestly, it sounds like you need a couple pass through parameters in a custom sql (since the week value really isn't a date but a string) to determine what weeks of your current year you would like to exclude from your sample?

1 of 1 people found this helpful
• ###### 6. Re: Excluding the current incomplete week from Year over Year calculations

Do you have a year identifier or no?

1 of 1 people found this helpful
• ###### 7. Re: Excluding the current incomplete week from Year over Year calculations

1) I'd suggest you create a date column in the format mm/dd/yyyy

2) each week number can be linked to either a single date relevant to that week or all the days that are relevant to that week.

3) get a LOD to exclude current week calculated on top of this date column

sample data may look like this

1 of 1 people found this helpful