1 2 Previous Next 16 Replies Latest reply on Oct 18, 2018 3:34 PM by Sarah Wilson

    Track text and date changes week over week

    Sarah Wilson

      Hello.  Trying to get a count of the # of times a requisition changes week over week.  Packaged workbook attached.  I tried using the lookup function but it did't work out.

       

      Background:  I'm taking a snapshot of open requisition data once a week.  I'd like to track/count the changes to the projected start date and Geo for my requisitions.  How would you go about this?  See attached spreadsheet.  For example, req # S0980 changed projected start date three times so I'd want Tableau to display: 3.  Thanks in advance!

       

        • 1. Re: Track text and date changes week over week
          Don Wise

          Hi Sarah,

          Does the attached and below work for what you're trying to do?  Thx, Don

          Screen Shot 2018-10-15 at 2.24.49 PM.png

          Screen Shot 2018-10-15 at 2.25.25 PM.png

          1 of 1 people found this helpful
          • 2. Re: Track text and date changes week over week
            Sarah Wilson

            Thanks, Don!  How would you go about creating two different calcs: one for # of geo changes and one for # of projected start date changes?  In addition, I want just the totals.  I don't want to display the req details.

             

            Appreciate your screenshots as I am on version 10.5 and am unable to open your twbx.

            • 3. Re: Track text and date changes week over week
              Don Wise

              Hi Sarah,

               

              No problem...I'll need a bit more context on your expectation...so what would a GEO example result look like?  Looking at the data, I'm not seeing much if any difference for GEO related to each individual Requisition #; only Feb and July weeks have some slight changes if I use a similar calc for GEO?

               

              Additionally, based on the original request of wanting to capture week-by-week, this is what I'd be looking at but you might want something different.  The reason I showed the level of detail on the prior is simply to show how it's calculating/working for your set of data.

              Screen Shot 2018-10-15 at 3.17.14 PM.png

               

              If using a similar calc as Projected Dates, the numbers for GEO would be very similar.  That's the hard part about supplying answers sometimes...we don't always have the context. 

               

              GEO calculation: {FIXED [Req #] , [Geo]: COUNTD([Geo])}

              Screen Shot 2018-10-15 at 3.27.41 PM.png

              I'll package a 10.5 version on your clarification for GEO...thx!  Don

              • 4. Re: Track text and date changes week over week
                Sarah Wilson

                Appreciate your time spent on this.  The fixed calc is giving me really high numbers that I don't think are correct.

                 

                I want a single number telling me how many times the geo changed for individual reqs.  Nevermind the week over week.

                 

                For example:

                Req # E0770 changed Geo on 8/6/2018.  That would count as 1 change.

                Req # E0788 changed Geo on 8/6, 8/27, and 9/4.  That would count as 3 changes.

                 

                In this example, I'm looking for 4.  Total changes.

                • 5. Re: Track text and date changes week over week
                  Don Wise

                  Hi Sarah,

                   

                  The GEO calculation actually turned into a variety of Table Calculations and the needed use of the Index() function to make the following work. Had to get creative. The level of detail in the view will have to stay in order for all the Table Calculations to work.  Sorry! 

                   

                  Regardless, I think you have the view and numbers you're looking for...hopefully!

                   

                  Newly attached 10.5 workbook for you.  Thx, Don

                  Screen Shot 2018-10-15 at 5.50.51 PM.png

                  Screen Shot 2018-10-15 at 5.51.08 PM.png

                  Screen Shot 2018-10-15 at 5.50.51 PM.png

                  • 6. Re: Track text and date changes week over week
                    Sarah Wilson

                    Thanks, Don.  I need only the aggregate.  Maybe I need to get the differences outside of Tableau first.  I'll try my noob SQL and r skills.

                    • 7. Re: Track text and date changes week over week
                      swaroop.gantela

                      Sarah and Don,

                       

                      Apologies for jumping in the middle.

                      Further apologies if I haven't caught the subtleties and have gone off base.

                       

                      I had the feeling that Don's initial approach would be fruitful.
                      If you are not looking at week-over-week changes and just need the aggregate,

                      you can try counting the number of geo changes on a req# level using:

                      { FIXED [Req #] : COUNTD ( [Geo] ) } - 1

                       

                      Likewise for start date changes:

                      { FIXED [Req #] : COUNTD ( [Projected Start Date] ) } - 1

                       

                      This should get you the geochange of 1 for E0770 and 3 for E0788.

                      You could aggregate this by removing Req# from the detail and

                      using SUM(GeoChangeCount).

                       

                      If there are other things going on with your worksheet, you may

                      need to use a window_sum or {SUM(GeoChangeCount)}

                      to get the total.

                       

                      Apologies again if I have jumped in blindly and missed the boat.

                       

                      Please see workbook v10.5 attached in the Forum Thread.

                       

                      284872count.png

                      • 8. Re: Track text and date changes week over week
                        Sarah Wilson

                        Thank you!  I think this what I'm looking for! I will dig in a little further tomorrow. 

                        • 9. Re: Track text and date changes week over week
                          Don Wise

                          Hi Swaroop!

                          I just learned something new from you ! I think your method of LOD aggregation for Sarah will work out well. 

                           

                          And I never mind the jump in...always worthwhile.  Thank you!  Don

                          • 10. Re: Track text and date changes week over week
                            Sarah Wilson

                            Thanks again, Swaroop!  Your calc is exactly right for the overall aggregate.  I will mark as the correct answer.

                             

                            Now what if I want to layer in the dates?  Sorry, I'm having a hard time describing what I need.

                             

                            My business case is this:  Recruitment can't fill positions on time when the forecast is constantly changing.  For example, if a position is moved from Toronto to San Jose, recruiting needs to start the hiring process all over again.  We want to show the trend of geo and start date changes.  All of our data is organized by projected start quarter since we work towards quarterly fill goals.

                             

                            I'm trying to show the trend of changes by Projected Start Quarter viewed from Date to Date.  For example, as of Dates 3/5, 6/28, 8/13 and 10/11 how many geo changes and start changes occurred for positions projected to start in Q2, Q3, Q4?  (Fiscal year starts in Feb)  Am I making any sense?

                            • 11. Re: Track text and date changes week over week
                              swaroop.gantela

                              Sarah,

                               

                              Thank you for the helpful description of the business case.

                               

                              Hmm. I'm not terribly keen on what I've come up with.

                              I think there must be a simpler way. Putting this out there for now, but will revisit.

                               

                              Definitely open to other suggestions from the community.

                               

                              The main problem is keeping the running count of the changes

                              when there are gaps between consecutive dates.

                               

                              Hardly anyone ever likes to do this, but I made a scaffold of dates

                              running from 1/1/2018 to 12/31/2019.

                               

                              I joined this to the data where [Scaffold Date] >= [Date]

                               

                              Then off the bat I filtered out those without changes:

                              { FIXED [Req #] : COUNTD ( [Geo] ) } > 1

                               

                              Then flagged the geo changes:

                              IF ATTR([Geo])<>LOOKUP(ATTR([Geo]),-1)

                              THEN 1 ELSE 0 END

                               

                              Then summed those up:

                              WINDOW_SUM([GeoChangeFlag])

                               

                              Required some very particular settings for the table calculations.

                              Then got the below graph of the running number of changes to Geo

                              (the top shapes are just for my checking).

                               

                              Please see workbook v10.5 attached in the Forum Thread.

                              Will revisit with a clean slate.

                               

                              284842reqcount.png

                              • 12. Re: Track text and date changes week over week
                                swaroop.gantela

                                Sarah,

                                 

                                Ok, here is a much more straightforward method,

                                but it just shows it as a bar graph.

                                 

                                Same as before,

                                filter out the nochanges with

                                { FIXED [Req #]:COUNTD([Geo])}>1

                                 

                                Flag GeoChanges with

                                IF ATTR([Geo])<>LOOKUP(ATTR([Geo]),-1)

                                THEN 1 ELSE 0 END

                                 

                                Then get a running sum:

                                RUNNING_SUM([GeoChangeFlag])

                                 

                                Did the same for ProjectStart.

                                The numbers look high, as was noted by Don.

                                 

                                Table calculations settings below.

                                Please see workbook v10.5 attached in the Forum Thread.

                                 

                                284842reqcountSettings.png

                                284842reqcountBar.png

                                • 13. Re: Track text and date changes week over week
                                  Sarah Wilson

                                  This is excellent!  Thank you.

                                   

                                  I agree that the start date changes look high.  I will dig into the data a little further to find out what's going on.

                                   

                                  Thanks again!

                                  • 14. Re: Track text and date changes week over week
                                    Sarah Wilson

                                    Me again!  I'm trying to reproduce your work.  I copied the fields and triple checked the table calculations but I'm coming up with a different view.  As soon as I switch from the automatic line chart to bars, the view displays the running sum times # of reqs.  I imagine this is something to do with the table calc but I can't seem to find the difference.  What am I missing?

                                     

                                    New twbx attached.

                                     

                                    1 2 Previous Next