August 6, 2012
Tableau Tip: Automatically excluding or keeping only the current week (without manual intervention)
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
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
Step 3 – Create a calculated field to act as the filter
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”
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:
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.
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.
ReplyDeleteYou 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.
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.
ReplyDeleteIs it possible to default the filter value to the week previous to the latest loaded one,not to the current one?
ReplyDeleteIt should work if you change add -7 after each of the today() references.
ReplyDelete@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)
ReplyDeleteThere 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.
ReplyDeleteHello Andy,
ReplyDeleteI 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
You can use the last() function and set it to calculate along your date field. Then choose the appropriate number of records.
DeleteCan 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.
ReplyDeleteYou can use the lookup function to previous 3 weeks data to show on the current 3 weeks.
Delete