12 Replies Latest reply on Dec 13, 2017 3:48 AM by DHEERAJ ARORA

# Find Active Items between Start and End Date

I am facing a challange in tableau to create a line chart which shows last 13 month trending.

I want every PRSN_INIT_ID should be spread month over month with the range of EFBEG_DT and EFEND_DT

So that I can make the trend chart to show how much employees contributing(Contribution Rate) month over month.

I wants to make this in tableau but unable to so so please help me to solve this.

I found many links on tableau community but those were not fulfill my requirement. I want the data should come in order to the below given Expected Result section.

Here is the data:

 PRSN_INTN_ID EFBEG_DT EFEND_DT Contribution_Rate EMPL_STAT_CD A0111 10/25/2015 6/30/2016 4 ACTIVE A0222 12/1/2016 2/28/2017 3 ACTIVE A0333 1/1/2017 4/1/2017 0 ACTIVE

Minimum Date: 10/25/2015

Maximum Date: 4/1/2017

Expected Result:

 Month Active Employee Employee Who Active 15-Oct 1 A0111 15-Nov 1 A0111 15-Dec 1 A0111 16-Jan 1 A0111 16-Feb 1 A0111 16-Mar 1 A0111 16-Apr 1 A0111 16-May 1 A0111 16-Jun 1 A0111 16-Jul 16-Aug 16-Sep 16-Oct 16-Nov 16-Dec 1 A0222 17-Jan 2 A0222, A0333 17-Feb 2 A0222, A0333 17-Mar 1 A0333 17-Apr 1 A0333
• ###### 1. Re: Find Active Items between Start and End Date

Start out by creating two parameters; one for Start Date and one for End Date.

Create a calculated field called DateRange or something similar.

[EFBEG_DT] >= [Start Date] AND [EFEND_DT] <= [END DATE] ---- I would recommend basing the date off of one date field like EFDATE.

Drag the created field to the filters shelf and set it to True.

Build your worksheet as normal.

• ###### 2. Re: Find Active Items between Start and End Date

Thanks Tim for help...!!

Tim I have tried this but this does not fulfill my requirement.

I want the view from the given data is like the below one viz.. The data has to auto spread month wise and the count of employee have to shown like I am showing below.

• ###### 3. Re: Find Active Items between Start and End Date

Can you attach your .twbx file here?  I should be able to fix it up for you.

• ###### 4. Re: Find Active Items between Start and End Date

Hi Tim, Please use the below link to access the workbook. Thanks...!

• ###### 5. Re: Find Active Items between Start and End Date

I am working in version 10.4 so I cannot save and send this back to you in 10.1.  The screen shot below shows what I have done.  The only real difference is I change the status to a measure.

• ###### 6. Re: Find Active Items between Start and End Date

Tim, this does not fulfill the requirement.

I wants the visualization as pasted below by using the same data I am having.

• ###### 7. Re: Find Active Items between Start and End Date

You will have to create a calculated field that gives that continuing value to the months in which your data does not have information and graph those points created in the calculation.

• ###### 8. Re: Find Active Items between Start and End Date

Dheeraj,

Please see if this alternative method could be useful:

CROSS JOIN with Tableau's join dialog

For the line graph, this technique is needed to show zeros:

Re: Showing missing dates in a Line Graph

Attached in the forum post is an example.

1 of 1 people found this helpful
• ###### 9. Re: Find Active Items between Start and End Date

Thanks..! Swaproop really Helpfull...!!!

First link CROSS JOIN help a lot, I have tested this with excel datasource and results looks good.

I am having a constraints that I am using DB2 database and do not have the rights to create lookup table into the database this is PROD environment. Also I have tried this cross join approach by using data blending but this would not work.

• ###### 10. Re: Find Active Items between Start and End Date

Hi Swaroop, I have published my workbook on public server with the results.

Tab cross join showing correct results.

Tab Blend have to work if there is any way to resolve my issue.

Please use the leow link to access the tableau workbook.

• ###### 11. Re: Find Active Items between Start and End Date

Dheeraj,

I'm not sure, but maybe you can try this technique:

Re: Headcount, when given the Arrival & Departure Dates

Also, please see attached in Forum Post.

• ###### 12. Re: Find Active Items between Start and End Date

Hi Swaroop, I have checked but this did not work.