2 Replies Latest reply on Feb 6, 2017 12:33 PM by Dana Chaffin

    Net Change - Details Behind Change

    Dana Chaffin

      I've created a weekly progression chart of requisitions in our system. It shows, for example, that Week 1 there were 100 requisitions and Week 2 there were 90 for a decrease in 10 reqs.

       

      Now, behind that net change of 10 there could have been 50 fills and 40 created

       

      The data comes in an Excel sheet of active reqs with the Run Date, Requisition Number, etc, and I append the master Excel sheet to feed Tableau. If a requisition is filled it just falls off the report.

       

      I've been unsuccessful in creating a calculation that would compare this week's Req # with the previous week's Req #, see which ones are in both weeks (active), were there but aren't now (filled) or ones that weren't there but are now (new) and apply a count, sum of 1s, or other calculations to show the details behind the change

       

      Any help would be greatly appreciated

       

      Thanks

       

      Dana

        • 1. Re: Net Change - Details Behind Change
          Dana Chaffin

          Update with upload:

           

          I've uploaded a sample of what I'm attempting to do (and not succeeding). I'll walk you through my latest logic which is in the upload.

           

          Step 1: Because the req #s are in the same column, and I only want a subset of those req numbers (early point in time and later point in time) I created a calculated field (for simplicity, I hardcoded it to be January and February) and called this column Req Group

          IF MONTH([Date]) = 1 OR MONTH([Date]) = 2 THEN [Req] END

           

          Step 2: I tried different variations and logic (varying success) - my latest attempt is in the upload. There are two columns - one is Count Req Group and the other is Count Distinct Req Group (and this is my downfall, as you'll see later, and either need help overcoming or someone point me to another solution )

           

          Step 3: Another calculation which isolates January Req Group and February Req Group and compares the Count Req Group number to the Count Distinct Req Group Number. If they equal, I have a winner (basically, I'm looking for one occurrence of the req number in the Req Group column and, depending on which month, it's either Filled/Cancelled or it's New)

           

          IF MONTH(ATTR([Date])) = 1 THEN

              IF [Count Req Group] = [Count Distinct Req Group] THEN "Filled/Cancelled" END

          ELSEIF MONTH(ATTR([Date])) = 2 THEN

              IF [Count Req Group] = [Count Distinct Req Group] THEN "New" END

          END

           

           

          Where it falls apart is I can't aggregate further

           

          I just want the categories, no req detail. Below is a screenshot of what I almost want (just imagine the 0, 2, 1, 1, are gone and the Blue Squares are replaced with numbers - I'll be hiding the Null)

           

           

          2-5-2017 6-24-30 AM.png

           

          Things I've tried so far:

          • Count Req Group only (and if it equals 1 then winner)
          • Blending to compare January Reqs to February (similar to a VLOOKUP) - I've never Blended data before. I searched and watched a lot of video. This may be the answer, I just don't know enough about Blending to make it work

           

          Any help would be greatly appreciated

           

          Thanks

          • 2. Re: Net Change - Details Behind Change
            Dana Chaffin

            Solved:

             

            Looked at the problem from a date standpoint instead of a Req # standpoint I discovered the solution. I still created a calculated field to isolate the reqs (Step 1 from above) and then went from there

             

            Step 1: Because the req #s are in the same column, and I only want a subset of those req numbers (early point in time and later point in time) I created a calculated field (for simplicity, I hardcoded it to be January and February) and called this column Req Group

            IF MONTH([Date]) = 1 OR MONTH([Date]) = 2 THEN [Req] END

             

            Step 2: Create two new calculations Earliest Date and Latest Date

            { FIXED [Req Group]: MIN([Date])} and { FIXED [Req Group]: MAX([Date])}

             

            Step 3:Identify "Filled/Cancelled" vs "New" by comparing Earliest Date and Latest Date in a new calculation called Filled/Cancelled/New

            IF MONTH([Date]) = 1 THEN

                IF [Earliest Req Date] = [Latest Req Date] THEN "Filled/Cancelled" END

            ELSEIF MONTH([Date]) = 2 THEN

                IF [Earliest Req Date] = [Latest Req Date] THEN "New" END

            END

             

            Put Filled/Cancelled/New in ROWS, Filter out NULLS, and move Req Group (aggregate to Count) as Text in the Marks and done

             

            2-6-2017 12-31-35 PM.png