6 Replies Latest reply on Feb 5, 2019 10:36 AM by Caitlyn Garger

# Table Calc to identify change from prior value and to show both on single record

Each week I append the full employee roster into a table. What I want to do now is create a report that identifies when someone's department changes (it does not = that same employee's department from the prior week.) Thus the bolded record below...

Then I want to filter out all other records and just show the changed value and the prior value on a single record, like step 2 below.

Anyway to do both, or at least to just filter the changed records if I can't do step 2?

 Raw Data As of Date Employee# Name Department 1/1/2013 12345 Jane Doe ABC 1/1/2013 22222 John Smith XYZ 1/1/2013 55555 Mike Green GGG 1/8/2013 12345 Jane Doe DEF 1/8/2013 22222 John Smith XYZ 1/8/2013 55555 Mike Green GGG 1/15/2013 12345 Jane Doe DEF 1/15/2013 22222 John Smith XYZ 1/15/2013 55555 Mike Green GGG Step 2: some Lag/Lead function to bring the prior value onto the same record as the changed value As of Date Employee# Name Department Prior Dept 1/8/2013 12345 Jane Doe DEF ABC Only want to keep the changed rows, and their respective prior value
• ###### 1. Re: Table Calc to identify change from prior value and to show both on single record

Dan,

Here's a calculation and a filter that accomplishes the goal:

The lookup table calc gives the previous value (you'll have to set the fact that it restarts every Employee #):

Lookup(ATTR([Department]), -1)

The filter is just a check to see if it is different from the current value.

I've attached the workbook and am happy to supply any additional explanation!

Joshua

2 of 2 people found this helpful
• ###### 2. Re: Table Calc to identify change from prior value and to show both on single record

Simple, yet brilliant...thanks Joshua

• ###### 3. Re: Table Calc to identify change from prior value and to show both on single record

Hi Josh,

I have a question about your solution here. (http://community.tableau.com/message/196866#196866) How did you restrict your lookup of the previous value to within the records for a single employee?  I downloaded your example workbook and tried to replicate it for a somewhat similar problem, but my implementation is picking up the previous row, which is for a different entity (in my case, it is farms, not employees).

This may be related to the fact that I bring in 4 (months, 1 record/month) worth of records for each farm, but I do a quick filter on the months so that I can see only the current month.  When I define the "previous values" for my field of interest, it then picks up the previous farm instead of the previous month value for the same farm?

I can send a packaged workbook, but I would need to pare it down considerably due to confidentiality restrictions, and I am never sure if just hiding fields removes them from a refreshed extract and subsequently created twbx file?

Thanks,

Buzz Burhans

• ###### 4. Re: Table Calc to identify change from prior value and to show both on single record

Buzz,

I'll have to refresh my memory on what I was doing and get back to you on what I was doing (ping me if I don't get back to you in a day or two).  In the meantime, check out Jonathan Drummey's explanation of table calculations: http://community.tableau.com/message/202808#202808.  It is one of the best explanations I've seen.

Regards,

Joshua

• ###### 5. Re: Table Calc to identify change from prior value and to show both on single record

Hello Joshua (and Buzz) - I am super glad I found this thread as I have been grappling with this same issue. Unfortunately, I have run into the same problem that Buzz ran into - which is that it is looking at the value of the previous row, not restricting its lookup to the same employee.

In my instance, I have prospects moving through stages and between group - same construct. I have one row per prospect per month with a stage and a group field. I need to measure movement from specific groups and stages to other groups and stages as part of performance management.

I would love any assistance in conquering this obstacle if either of you ever found a proper solution. Many thanks!

Benjamin

• ###### 6. Re: Table Calc to identify change from prior value and to show both on single record

Hello Joshua,

I was wondering if you could help me with a similar problem. I have tried a multitude of different things to try and get exactly what I want but have had no luck so far. I have a this problem in question posted on the forum but haven't had any luck with the responses so far and was wondering if you had any insight that could help.

Here is the posted question: Lookup Value

Any guidance and/or insight would be much appreciated!

Caitlyn