2 Replies Latest reply on Jun 14, 2016 4:40 PM by John T

Date Calculation: Daily Weekday Data, Need to Only Select First Entry for Each Month

Hi all,

Hoping you can help me with something regarding dates. Basically, I have daily weekday data for a whole year and am pulling together a data table that displays a data point for each month. However, I need Tableau to automatically pull just the first data entry for each month. The tricky thing is I have weekday data so if the first of the month is a Sunday, I'tll have to pull the second.

For 2015, the data that should be pulled in is as follows:

2015

• January: 1/1
• February: 2/2
• March: 3/2
• April: 4/1
• May: 5/1
• June: 6/1
• July: 7/1
• August: 8/3
• September: 9/1
• October: 10/3
• November: 11/1
• December: 12/1

Anyone have a creative way to get around this? Another thing I'll have to solution for is the fact that the first of each month is representative for the last month's total. So, the 3/2 data is actually a summary of February's data. So, I'll have to find a way for Tableau to display that (which is a bit tricky because 1/1/15 is actually the summary data for December 204)

• 1. Re: Date Calculation: Daily Weekday Data, Need to Only Select First Entry for Each Month

There's a couple of ways to solve for this, here's one approach using Level of Detail calculations.

I've created a level of detail calculation to figure out when the first weekday is of each month;

{FIXED [mmmyy] : MIN(if DATEPART('weekday',[Order Date])>1 and DATEPART('weekday',[Order Date])<7 then [Order Date] end )}

Then I create a calculated field to sum the sales values on those dates only;

sum(if [Order Date]=[FirstWeekday] then  [Sales] end)

I'm using a helper field called mmmyy to allow me to do a FIXED LOD calculation at the Month and Year level.

• 2. Re: Date Calculation: Daily Weekday Data, Need to Only Select First Entry for Each Month

Thanks Tom, worked great!