This would be helpful, if you can share the sample workbook.
Arthur, this is a classic "fill in the missing dates" problem.
The short answer is that you need a baseline calendar (every single day/month/year as a row) left-joined to your "In Service" data set. This gives Tableau an anchor for figuring out whether a given item is in service on a given day.
Since your precision is at the Month level, you likely can get away with month/year for your baseline calendar.
Search the forums for "missing dates" for some detailed ideas.
This is the excel file I have on the data set.
As you can see, on the excel file, I have the Months In Service on Column B and Date In Service (meaning start date) on Column X. If you go to Column AC, I've created a End-Date column by using =DATE(YEAR,MONTH,DAY) to calculate that. Now, I want to be able to see on tableau at any given month, how many contracts were in service at that time.
SampleDataForTableau.xlsx 1.8 MB
Please see my response to TG above! Perhaps the data set can help explain my question a bit more thoroughly.
This solution assumes that you have an end date for your products. You can create the end date in Tableau by making a Calculated field with the Dateadd function with months identified as the date part and the number of months from your data source.
From there, this is a solution to your issue.
Create a Parameter for Date. This will be the day you are choosing to test for active products.
Make a calculated field with the following:
IF ([Test Date]>= [DTR In-Service Date] and [Test Date]<=[DTR End-Service Date]) THEN 'Active'
I named mine Count of Active. Pop that on your rows, Put SUM(number of records) as Text. Choose Show parameter control for the test date.
From there, select the day you want to check and BINGO. Count of in service products.
Thank you Andrew!
This is perfect. Now, is there a way to create a graph rather than just have the number from this method? If convenient, do you mind providing a private contact info for a separate question?
You can build a bunch of different graphs off of this. It all depends on what you are looking for. What do you want the separator to be. Day? Month? Start Date? End Date?
You have a bunch of options from here.
I don't mind sending you my contact info but I don't want to post my email publicly. If you Follow me from my profile page, I will be able to direct message you my info.