6 Replies Latest reply on Sep 28, 2018 2:32 PM by Gerardo Varela

    Work In Progress

    Charlie Lehmann

      I have a worksheet of requests that have two dates associated with each row, one is created date and one is completed date.  I would like to calculate the number of requests in each month that are in progress.  This is easy in Excel as I can define the date in my chart and compare the created and completed date to that reference date by looking at what reference dates are between the created and closed dates, but the same calculation can't be done in Tableau as I can't define the number of records as a date.

        • 1. Re: Work In Progress
          Ken Flerlage

          Any chance you could provide a small sample of your data as well as the expected output?

          • 2. Re: Work In Progress
            Joe Oppelt

            I kind of followed your description, but at the end I ended up confused.

             

            I agree with Ken.  A sample workbook would really help me see what you are trying to do.

             

            If proprietary data or an oversized data set is an impediment to uploading a sample, consider following the steps here:

             

            Video demonstrates how to anonymize your workbook/data

             

             


            I can say this though.  If you want to compare some "anchor" date and see if jobs are in progress as of that date (I assume in-progress would mean no completion date, or a completion date after the anchor date), then give the user a parameter to select a date and work against that.

             

            IF [Start Date] <= [Anchor Parameter Date] THEN  // this one needs to be evaluated

                IF (isnull([Completion Date]) or [Completion Date} > [Anchor Parameter Date])

                THEN "Yell and someone" ELSE "You get an atta-boy!"

                 END

            ELSE      // Start Date after anchor.  Ignore.

            ""             // NULL

            END

            • 3. Re: Work In Progress
              Charlie Lehmann

              Thanks Ken - I couldn't publish my workbook, but here is an example of my data....

               

              RequestQueueCreatedDateClosedDate
              211Translations1/5/20165/24/2017
              371Translations1/29/20162/22/2016
              372Translations1/29/20164/1/2016
              373Translations1/29/20162/26/2016
              374Translations1/29/20162/26/2016

               

              And this is the expected result...................

               

              REQUESTS IN PROGRESS (WIP)
              Total ReqTranslationsTeleformPilot
              1/1/20179376170
              2/1/2017139117220
              3/1/2017158136220
              4/1/2017171147240
              5/1/201710079210
              6/1/2017147113340
              7/1/2017177139380
              8/1/2017182146279
              9/1/20171831472214
              10/1/2017172147205
              11/1/2017215188189
              12/1/2017244212239
              1/1/20181381052211
              2/1/20181451082215
              3/1/20181511082221
              4/1/2018122851324
              5/1/2018129771933
              6/1/2018138992217
              7/1/201812398916
              • 4. Re: Work In Progress
                Joe Oppelt

                Tableau is not cell-oriented like excel.  It's dimension-oriented, and it's data driven.

                 

                You need a row of data to make a mark.  If a project starts in June and ends in September, you need a row for it in July to make a "mark" where you can place a tick, and then come back count all the ticks in July to see how many jobs were open in July.

                • 5. Re: Work In Progress
                  Gerardo Varela

                  Hi All,

                      Attached is a start. Everything is explained in this thread (kind of):

                   

                  Items Active between Start and End Date

                   

                  I just used the workbook Lukaz posted, made duplicates of the fields, and renamed them to what Lukaz had in his workbook.  Then I just replaced the data source and voila.  Please head on over there for an explanation.

                   

                  Regards,

                  Gerardo

                  • 6. Re: Work In Progress
                    Gerardo Varela

                    Here is the workbook.

                     

                    Regards,

                    Gerardo