7 Replies Latest reply on Jul 11, 2017 9:30 AM by Andrew Kelley

# # of Products at a specific point in time

Hi Tableau Community,

I have been faced with a small task and cannot figure it out. I have a large excel spreadsheet of data (6000 + rows) and would like to see how many products are "active" at any given point in time. What is provided is the number of months that a product is active and the month START date for the product's activity.

For example:

MONTHS IN SERVICE                                                  IN SERVICE START DATE

45                                                                                   May-2012

50                                                                                   April-2010

29                                                                                  September-2011

Is there a way to be able to figure out on tableau at a specific given month, how many products are active or do I have to add in the end date through excel?

• ###### 1. Re: # of Products at a specific point in time

Hello Arthur,

This would be helpful, if you can share the sample workbook.

TG

• ###### 2. Re: # of Products at a specific point in time

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.

• ###### 3. Re: # of Products at a specific point in time

Hi TG,

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.

Thank you.

• ###### 4. Re: # of Products at a specific point in time

Hi Michael,

Please see my response to TG above! Perhaps the data set can help explain my question a bit more thoroughly.

Thank you,

Arthur

• ###### 5. Re: # of Products at a specific point in time

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'

ELSE 'Exclude'

END

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.

A.

• ###### 6. Re: # of Products at a specific point in time

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?

Thank you,

Arthur

• ###### 7. Re: # of Products at a specific point in time

Sure,

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.

Thanks,

A.