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 ????

    Yogesh Pareek

      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 ????
          Rody Zakovich

          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

           

          http://redheadedstepdata.io/lookup-vs-transactional/

           

          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 ????
            Yogesh Pareek

            Hi Rody,

             

            Many thanks for your help!

             

            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 ????
              Rody Zakovich

              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]) })

               

               

              If you aren't sure about LoDs, please refer here

               

              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 =

              DATEADD('year', -1, TODAY())

               

              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 ????
                Yogesh Pareek

                Hi Rody,

                 

                Thanks for your help!

                 

                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 ????
                  Yogesh Pareek

                  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 ????
                    Yogesh Pareek

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

                     

                         

                    Request IDStartEndPlant CountrySupplier Location
                    14/9/201512/13/2016AUSUK
                    21/18/20155/16/2016AUSUK
                    34/8/20154/13/2016AUSUK
                    44/11/20165/16/2016AUSUK
                    54/11/20165/17/2016AUSUK
                    64/11/20165/17/2016AUSUK
                    78/24/201512/28/2016AUSUK
                    84/11/20165/12/2016AUSUK
                    94/11/20165/12/2016AUSUK
                    109/5/201610/7/2016AUSUK
                    114/15/20165/15/2016AUSUK
                    129/1/201611/1/2016AUSUK
                    139/12/201611/1/2016AUSUK
                    149/1/201611/1/2016AUSUK
                    158/8/20169/4/2016AUSUK
                    168/8/20169/4/2016AUSUK
                    178/8/20169/4/2016UKUK
                    184/7/20164/14/2016UKAUS
                    194/7/20164/14/2016UKAUS
                    206/15/20165/15/2016UKAUS
                    219/1/201611/1/2016UKAUS
                    229/1/201611/1/2016UKAUS
                    239/1/201611/1/2016UKAUS
                    248/12/20169/4/2016UKAUS
                    258/8/20169/4/2016UKUK
                    268/8/20169/4/2016UKUK
                    271/7/201612/14/2016UKUK
                    281/1/201612/14/2016UKUK