12 Replies Latest reply on Jun 9, 2017 1:03 PM by Becki Duong

    Progress Metrics

    Becki Duong

      Hi,

       

      I'm having trouble trying to find an efficient way to measure the monthly progress in tableau. My data source refresh monthly, hence the number in the bar graph below will change month to month after data being refreshed. I only want to capture the differences (variations) of the red highlighted bar.

       

      For example:

      In May, there are 81 Data defects for the deployment HVAC NA. However, in June, it reduces down to 49 Data defects. Which mean within a month, 32 defects have been resolved. That's mean approximately 60% progress have been made.

       

      Is there a way where Tableau can capture the metrics before data refresh and measure it against the new data? Any help would be greatly appreciated.

       

                                                 

       

       

      Regards,

      Becki

        • 1. Re: Progress Metrics
          Sherzodbek Ibragimov

          Hello,

          This article could be a good start without seeing your sample data to show the instruction:

          Olga Tsubiks - Data Visualization, Analytics and Digital Marketing

          Hope it helps.

          Sherzod

          • 2. Re: Progress Metrics
            Charles Miles

            Hi Becki,

             

            It sounds like you are looking for tableau to basically do an output of your monthly data so that you can track it over time since the table refreshes monthly. Is that correct? Tableau does have this functionality, but you'll have to create a date field with your data connection. This will require you creating a date field from your connection, which means a custom sql query or initial sql query that creates that date field.

             

            It'd look something like this in a custom sql query:

            DATEADD('day',INT(RIGHT([StringDate],2)),DATEADD('month',INT(MID([StringDate],3,2)),DATEADD('year',INT(LEFT([StringDate],4)),#1/1/1900#))) // gives MM/DD/YYYY

             

            Here is a how to on the adding of the data to your extract: Add Data to Extracts

             

            A better solution would be to ask your IT department to create a historical view of your refreshed data set.

            1 of 1 people found this helpful
            • 3. Re: Progress Metrics
              Becki Duong

              Hi Charles,

               

              Thank you for your response. I have few questions regarding to your suggestion of adding a Date field. Would the date in the Date field be replace by the refresh date? For example, let say the last extract date was on May 1st, hence the Date field should be populated with "05/01/2017" for all data. On Jun 1st, new extraction was provided, hence we'll need to refresh the data. Will the date in the Date field be replaced by Jun 1st? or will another date field will  be created for each time it refresh?

               

              Ideally, I would only want to capture the summary output from Tableau for each refresh.

               

              Below are the two options I'm thinking of.

              Option A: Create new column/field for each refresh date. So we can see the progress over a set period of time.

              Option B: Only have two columns/fields, Previous and Current. The Previous will contain last month's defect count and Current will contain this month's count (refresh count).

               

               

              Thanks,

              Becki

              • 4. Re: Progress Metrics
                Charles Miles

                Hi,

                 

                No Problem. Please like/rate the comment if you could! I'm trying to bump my points up So, given the nature of what you are trying to accomplish, putting it in that format might be annoying. If you can't do that, I'd build a sheet that looks more like this:

                It will become a long running spreadsheet, but it will give you the history you are wanting with a date field you can look at from a monthly perspective. If you are worried about the sheet getting too big, then I'd suggest adding the data to your extract: Add Data to Extracts and only entering values in the spreadsheet for current month. When a new month and new values come in and you've set up your data extract correctly as well as inputted the data for the current month, simply refresh the extract, and it will add the new month that you've entered to the extract.

                2 of 2 people found this helpful
                • 5. Re: Progress Metrics
                  Charles Miles

                  Also, to clarify, if you didn't want the spreadsheet to get too big and went with my second suggestion, you'd have to clear out the previous month's values when you add it to the extract, otherwise you'll have duplicate values for the previous month. If you don't care about how big the spreadsheet gets, the "add to data extract" and deletion of the old values is a mute point.

                  2 of 2 people found this helpful
                  • 6. Re: Progress Metrics
                    Becki Duong

                    Hi Sherzodbek,

                     

                    The article you suggested wasn't what I am trying to do. But thanks for your input.

                     

                    Regards,

                    Becki

                    • 7. Re: Progress Metrics
                      Becki Duong

                      Hi Charles,

                       

                      We definitely would not want the file to get too big. I'm working with 20-30 millions records, so adding the currently month's data to the extract would not be an option.

                       

                      If previous month's value get clean out, then how could we measure the variation/ progress between previous and current month?

                       

                       

                       

                       

                      • 8. Re: Progress Metrics
                        Charles Miles

                        Honestly, Becky, I'd recommend talking to your IT. This seems more like a data structure/storage issue than an issue you are going to be able to solve with Tableau. The data will continue to add onto a Tableau Extract. And, given extracts are stored on your local machine, that would overload your personal computer quickly. It would be much easier to solve for this with a summarized defect table by month, then connect it to Tableau. Tableau can be good for manipulating information (to a degree) but when it comes to a problem like yours it's more of a server/data design issue than anything.

                        1 of 1 people found this helpful
                        • 9. Re: Progress Metrics
                          Charles Miles

                          Sorry, Becki! Gah, how embarrassing. why can't I delete my comment and make the edit? Grrrrr.

                          • 10. Re: Progress Metrics
                            Charles Miles

                            If you feel any of my responses have answered your question, please mark the response as answering the question. Thanks, good talking!

                             

                            Charles

                            • 11. Re: Progress Metrics
                              Becki Duong

                              The initial thought was to create a summary table that generate from Tableau, then manually update it monthly after the refresh. But the problem with that is there are thousand of rows and we need to measure the progress for 100+ projects. Hence I was hoping Tableau can perform and automatic some of the process. I guess you're right. It's more of a data design issue. I'll talk to my local IT guy. Thanks for all of your help!

                              • 12. Re: Progress Metrics
                                Becki Duong

                                No worries. :-)