7 Replies Latest reply on Mar 1, 2016 3:45 AM by Yogesh Pareek

# Urgent Need Help - Create Report to show number of active job in each month ????

Dear Friends,

Problem Statement: Create a graph to show number of active jobs in each months of five quarter. We have to consider current quarter, next three quarter and one previous quarter.

I have only three fields in the table:

1. Job ID

2. Job Id Start Date

3. Job Id End Date

How to calculate active job for a each month?

Example: Suppose we have three job id , first Job is starting in Jan 2015 and ending in April 2015. Second Job is starting in March 2015 and end in June 2015 and third job is starting in April 2015 and ending in Oct 2015.

Here Job 1 is active in Jan, Feb , March, April 2015

Job 2 is active in March , April, May , June 2015

Job 3 is active in April, May, June, July, Aug, Sep, Oct 2015

We should have a graph showing that in Jan active job is 1, for Feb active job is 1 , March 1, April 3, May 2, June 2, July 1, Aug 1, Sep 1, Oct 1, Nov - Null, Dec - Null.

Please suggest me the logic for this given report.

I have attached the sample data.

• ###### 1. Re: Urgent Need Help - Create Report to show number of active job in each month ????

Hello Yogesh,

The first thing we need to recognize is the structure of the data.

Have two separate fields of [Start] and [End] dates, is going to highly limit what is possible in Tableau, as Tableau needs a centralized "Date" field in order to properly plot your data points.

If possible, it would be best if you could Pivot you Datasource into two colums

1. Job ID

2. Dates (This has both Start and End)

For reason why to have your data in this structure, please refer here

Once you have your data pivoted, you can refer to this example workbook.

Counting Active Customers from Sparse Data

Be sure to check out the Comments for "Better"  solutions to the problem.

If pivoting the datasource is not possible, then you will need to setup a Scaffold Dataset that can be used to blend the data, and give you the structure you need.

The latter can get very complex, but this video should help you to achieve it

TDT: Data Scaffolding with Joe Mako

That said, there are limitation to it, and it should really only be used as a "Last Ditch Effort" when you can't re-shape the data on the backend.

Hope this helps, if you need any further assistance, feel free to ping me.

Best regards,

Rody

• ###### 2. Re: Urgent Need Help - Create Report to show number of active job in each month ????

Hi Rody,

I have a small problem here.

1. How can I restrict the graph for only to show result only for five quarter - One previous + current + Next three. It should use all the data but show active jobs only for five quarter.

I am able to see active jobs for a year but not for the five quarter.

2. Also I have a small different logic for calculating no of active jobs for one month.The count of active job/ request for each month is the total number of max jobs active during that time of the month.

Example :

If for month of Jan’2016, we have the following number of active jobs for each day of the month:

 Jan'16 No of Active Jobs Jan'16 No of Active Jobs 1-Jan 21 17-Jan 60 2-Jan 10 18-Jan 45 3-Jan 22 19-Jan 20 4-Jan 45 20-Jan 28 5-Jan 5 21-Jan 27 6-Jan 10 22-Jan 90 7-Jan 15 23-Jan 56 8-Jan 16 24-Jan 40 9-Jan 18 25-Jan 12 10-Jan 20 26-Jan 21 11-Jan 19 27-Jan 31 12-Jan 26 28-Jan 31 13-Jan 27 29-Jan 13 14-Jan 10 30-Jan 10 15-Jan 17 31-Jan 10 16-Jan 15

In the above example, the count of jobs for Jan’2016 = 90

Thanks,

Yogesh

• ###### 3. Re: Urgent Need Help - Create Report to show number of active job in each month ????

Hey Yogesh,

Given your criteria, I think the solution should be to pad your data on the backend, so that you have a single [DATE] column, which contains every individual Day Between the Start and End Date of each Job ID.

MasterDate would look like so in Excel, or as a Table/View in your DB

IF you are using EXCEL then you will need to use the LEGACY CONNECTION to use CUSTOM SQL Commands

Re: How to Generate Custom Sql querry in Tableau 9.0 like 8.1 version?

And your SQL would look something like this. Reference kettan Cross Join Collection The Cross Join Collection

SELECT

*

FROM

[Source\$] d

, [MasterDate\$] l

WHERE

d.[Start Date] <= l.[MasterDate]

and d.[End Date] >= l.[MasterDate]

This will give you a Data Structure like so, where you have every individual data between the Start and End Dates

This is critical in order for to Calculate the DAY with the MAX Active Jobs each month.

Once you have the Data in Tableau in a good format, we can use an LoD to find the MAX Active Jobs per Month

MAX ACTIVE JOBS =

MAX({ INCLUDE [MasterDate] : SUM([Number of Records]) })

lod expression | Drawing with Numbers

The next step is to create a Field that will Truncate the Date to a Monthly Level

DATETRUNC('month', [MasterDate])

Place that onto the Column Shelf, and Place MAX ACTIVE JOBS onto the Row Shelf like so

Given the number of Data Points, I am going to use Months, instead of Quarter to show you the filtering.

Since you example data had 2015, I created another Field that I will use as a Reference, if your actual data is of 2016, then you won't need this step.

Reference Date =

Once we have that we just need to setup a calculated field like so to filter

DATETRUNC('month', [MasterDate]) >= DATEADD('month', -1, DATETRUNC('month', [Reference Date]))

//For 2016 data this would be

// DATETRUNC('month', [MasterDate]) >= DATEADD('month', -1, DATETRUNC('month', TODAY()))

AND DATETRUNC('month', [MasterDate]) <= DATEADD('month', 3, DATETRUNC('month', [Reference Date]))

//For 2016 data this would be

// DATETRUNC('month', [MasterDate]) >= DATEADD('month', 3, DATETRUNC('month', TODAY())

Place that on the Filter Shelf and Select True.

This should give you your desired output format

*NOTES*

I used Month and a Reference date for the filtering because of your Sample Data. If your actual Data contains 2016, then you would substitute [Reference Date] with TODAY()

Also, instead of DATETRUNC('month', [Date]), you would use DATETRUNC('quarter', [Date]), you need to make this adjustment on the DATEADD parts of the calc as well.

The example Workbook is attached for your reference.

If you run into any trouble, or don't understand how and why we are doing this, please ping me.

Hope this helps,

Regards,

Rody

• ###### 4. Re: Urgent Need Help - Create Report to show number of active job in each month ????

Hi Rody,

I am not able to download workbook properly in my system. Can you please send it to me through email at yogesh.pareek@ge.com / yogesh.pareek@tcs.com

Thanks & Regards,

Yogesh

• ###### 5. Re: Urgent Need Help - Create Report to show number of active job in each month ????

Dear Rody,

I am facing one more issue in this report. I have one new requirement here:

Please refer the attached sample data for this requirement. I have two column supplier Location and Plant Country. I want to show all the distinct value of this two column in one Filter. And based on the selection I need to generate two line graph in one x-y axis. Suppose you select UK from the filter then it should take all the job where Plant country is UK and generate one graph. In the the second graph it should take all the job where supplier location is UK. Both Line graph should use only one x-y axis.

Here to calculate the max active job should be same as before and cont based on the selection.

Please consider the performance issue also as we may have more data.

I have attached the workbook and sample data for your use. let me know if you need any more details.

Many thanks for your help once again,

Thanks & regards,

Yogesh

• ###### 6. Re: Urgent Need Help - Create Report to show number of active job in each month ????

Did not able to add the workbook and sample data. Here are the Sample data columns you can use

 Request ID Start End Plant Country Supplier Location 1 4/9/2015 12/13/2016 AUS UK 2 1/18/2015 5/16/2016 AUS UK 3 4/8/2015 4/13/2016 AUS UK 4 4/11/2016 5/16/2016 AUS UK 5 4/11/2016 5/17/2016 AUS UK 6 4/11/2016 5/17/2016 AUS UK 7 8/24/2015 12/28/2016 AUS UK 8 4/11/2016 5/12/2016 AUS UK 9 4/11/2016 5/12/2016 AUS UK 10 9/5/2016 10/7/2016 AUS UK 11 4/15/2016 5/15/2016 AUS UK 12 9/1/2016 11/1/2016 AUS UK 13 9/12/2016 11/1/2016 AUS UK 14 9/1/2016 11/1/2016 AUS UK 15 8/8/2016 9/4/2016 AUS UK 16 8/8/2016 9/4/2016 AUS UK 17 8/8/2016 9/4/2016 UK UK 18 4/7/2016 4/14/2016 UK AUS 19 4/7/2016 4/14/2016 UK AUS 20 6/15/2016 5/15/2016 UK AUS 21 9/1/2016 11/1/2016 UK AUS 22 9/1/2016 11/1/2016 UK AUS 23 9/1/2016 11/1/2016 UK AUS 24 8/12/2016 9/4/2016 UK AUS 25 8/8/2016 9/4/2016 UK UK 26 8/8/2016 9/4/2016 UK UK 27 1/7/2016 12/14/2016 UK UK 28 1/1/2016 12/14/2016 UK UK