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

# Net Change - Details Behind Change

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

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)

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

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