Data Viz Done Right

August 6, 2012

Tableau Tip: Automatically excluding or keeping only the current week (without manual intervention)

10 comments

Situation: You have daily sales data, but you don’t want to include the current week until it closes.  You also don’t want to manually filter the most recent week out of the report each week because that’s an unnecessary nuisance.

Solution: Create two date calculations and one filter calculation

Step 1 – Create a calculated field to get the current year & week

image

Note that I’m creating a string that includes the year.  This is necessary if you have more than one year in your reports.  If the formula only returns the week number, then that week number would be excluded for all years.

Step 2 – Create a calculated field to get the year & week for each date of your date field

image

Step 3 – Create a calculated field to act as the filter

image

Step 4 – Drag the Exclude Current Week field to the Filters shelf and choose the Exclude option on the bottom right when the result of the calculation is “EXCLUDE”

image

An alternative solution would be to build the two calculated field from step 1 & 2 into the calculation built in step 3, but I chose to break them out separately for this example so that you can more easily see how the calculations work together.

The calculation would look like this:

image

This method would work if you wanted to ONLY KEEP the current week.  In this case, you would follow step 4, but you would NOT select the Exclude checkbox on the bottom right.

10 comments :

  1. In my opinion, that is overly complex for your stated goal. Although I can see that you may have designed this originally to perform more complex logic.

    You may want to consider a formula like:

    DATETRUNC('week',[Order Date])=DATETRUNC('week',TODAY())

    This will return "True" for dates in current week, and "False" for all others.

    This will lessen the query complexity.

    Additionally, you can use a pass-trough function for the most optimization.

    ReplyDelete
  2. Correct, that get to the same result, just a different calculation to get there. I broke it up into different calculations so that the reader could more easily understand how it all works.

    ReplyDelete
  3. Is it possible to default the filter value to the week previous to the latest loaded one,not to the current one?

    ReplyDelete
  4. It should work if you change add -7 after each of the today() references.

    ReplyDelete
  5. @Andy- If we add -7 after Today() it will work for the week previous to the current one. I'd like it to be the week previous to the last week loaded into the datasource (one of the weeks in the past)

    ReplyDelete
  6. There isn't an inherent field in Tableau that has the "last loaded" date. You would need to have that in your data set, then you can create the desired filter.

    ReplyDelete
  7. Hello Andy,
    I always have 4 .5 months of data in my database. But in my Tableau report I want to display only the past 4 weeks of data. Is there any way that I can do it automatically. I can always choose the weeks that I want. But I want to do it automatically to show only the past 4 weeks of data in the report.

    Thanks

    ReplyDelete
    Replies
    1. You can use the last() function and set it to calculate along your date field. Then choose the appropriate number of records.

      Delete
  8. Can anyone help me how i can show previous 3 weeks data along with selected date range. i.e. date range from 8jun-14jun, i should see this week avg and 18/5-7/6 range as well.

    ReplyDelete
    Replies
    1. You can use the lookup function to previous 3 weeks data to show on the current 3 weeks.

      Delete