There really isn't enough to go off of here. What's your actual question? What's your stumbling block? What's the structure of your data?
It would help a lot if you could attach a Tableau Packaged Workbook or a sample with the same data structure.
It's not really clear to me based on the first example or the attached example what your business logic is for each period. You've stated 35 days but I don't understand it's that 35 days before the 15th, 35 days after the 15th or either side.
I do think you're going to need to do this in SQL as you're effectively going to have to 'fake' records in the months where they don't exist. I'd imagine some type of a cross join or union, but it's hard to say without truly understanding your windows of time.
Tom: Sorry if I wasn’t clear. It is 35 days before the 15th. For example, on June 15 we look back 35 days and count the number of clients who had at least one visit.
I agree, I think I need to do something as part of the SQL query, I just can’t figure it out. I have sort of figured out a clumsy work-around using the makedate function to check if visits fall into the 35 day window each month. I’ll post that when I finish it.
Niki Le Prohn
Director-Data Driven Practice
Building on 50 Years of Investing in Hope | 1966-2016
I'm going to preface this with my usual 'there's many ways to skin a cat', but I see one hurdle with this problem which will be periods where 0 visits took place. Thus, we need to 'fake' the data in order for say June 2016 to show if no visits took place in the 35 days before June 15.
I normally accomplish this type of a thing in SQL by using Cross Joins. I've prepared an example here with a helper table which effectively contains a list of dates on the 15th for each month in 2016. There's a bunch of ways you can generate this table, you could save it as a Dimension table in your database for permanent re-use, or create it as a table function, or even just nest the sql within a SELECT statement without saving the object at all. The right answer will depend on your situation.
Then I created a fake visits table which includes a client, employee name and Visit Date. My sample data;
VisitDate Employee Client 2016-01-10 John ACME Solutions 2016-01-20 Peter ACME Solutions 2016-03-01 Sally ACME Solutions 2016-01-14 John Wombat Inc 2016-02-14 Peter Wombat Inc 2016-03-14 Sally Wombat Inc 2016-04-14 Sally Wombat Inc
The magic is in the final select statement where I start by selecting the dates from the ReportingDates table (the list of the 15th dates),cross join it to a distinct list of clients then left join it to the visits where the Visit Date is between the Reporting Date and 35 days earlier.
SELECT ReportingDate, Client, COUNT(VisitDate) VisitCount FROM ( SELECT d.ReportingDate, c.Client, v.VisitDate, v.Employee FROM #ReportingDates d CROSS JOIN (SELECT DISTINCT Client FROM #visits) c LEFT JOIN #visits v on c.Client = v.Client and v.VisitDate between dateadd(d,-35,d.ReportingDate) and d.ReportingDate ) a GROUP BY a.ReportingDate, a.Client
And my output in Tableau looks like;
I've attached some sample SQL utilizing temporary tables so you can replicate exactly what I've shown.
VisitReportings.sql.zip 720 bytes