10 Replies Latest reply on Feb 24, 2016 2:21 PM by J Frueh Branched to a new discussion.

# Second to last value by two fields

I've got a bit of a flu, so maybe it's just my brain having issues.  But any help is greatly appreciated.

I'm looking to get the second to most recent value for a field based on two other fields

Data looks like this:

 Snapshot Date Opportunity ID Stage Name 1/14/2016 12 95% 1/21/2016 5 80% 1/28/2016 20 65% 2/4/2016 35 50% 1/14/2016 5 75% 1/21/2016 20 35% 1/28/2016 35 25% 2/4/2016 12 15% 1/14/2016 20 25% 1/21/2016 35 95% 1/28/2016 12 65% 2/4/2016 5 50%

I want to create a new calculated field that shows the Stage Name from the PREVIOUS snapshot date.  So after it would look like:

 Snapshot Date Opportunity ID Stage Name Previous Stage Name 1/14/2016 12 95% NULL 1/21/2016 5 80% 75% 1/28/2016 20 65% 25% 2/4/2016 35 50% 25% 1/14/2016 5 75% NULL 1/21/2016 20 35% 25% 1/28/2016 35 25% 95% 2/4/2016 12 15% 65% 1/14/2016 20 25% NULL 1/21/2016 35 95% NULL 1/28/2016 12 65% 95% 2/4/2016 5 50% 80%

Please hook me up with some Tableau magic goodness.

• ###### 1. Re: Second to last value by two fields

That would be a calculation like this:

lookup(sum([Stage Name]),-1)

Click on the dropdown of the pill and select 'edit table calculations'. Select compute using advanced in the pop up and drag all fields from partitioning to addressing. Click ok and then restart every opportunity id in the main dialogue box:

• ###### 2. Re: Second to last value by two fields

Thank you for the reply.  On the plus side, what you wrote does work based on the incomplete ask of mine.  Unfortunately, it doesn't solve my problem

What I didn't mention is that the "Stage Name" field is not actually a number based field.  It's text.  And once I have the solution, I'll be using it for more fields than just Stage Name.

Additionally, I would like to do this as a "Calculated Field".

Can you help me out?

• ###### 3. Re: Second to last value by two fields

Anyone else can help?

I'm trying to get the Previous value for Stage name, by date using Snapshot Date, for each opportunity ID.

I'd like this to be a Calculated Field rather than being based on what is currently displayed in the worksheet.

Can this be done with Max somehow?

I've seen similar asks being suggested using a LOD  calculation with Fixed?

• ###### 4. Re: Second to last value by two fields

It doesn't matter if the field is a text or a number. Changing the aggregation from SUM to ATTR will still do the trick:

Although, I am not sure what you mean by "I'd like this to be a Calculated Field rather than being based on what is currently displayed in the worksheet." How would you reference what the previous value is if the dates are not in the view?

• ###### 5. Re: Second to last value by two fields

I know as a SQL code it could be done by using MAX and a less than.

Maybe this is just another thing that isn't possible in Tableau

Would a LOD calculation not work?

• ###### 6. Re: Second to last value by two fields

Ok maybe another way is to create 4 calcs:

Max Date: { fixed [Opportunity ID]: max([Snapshot Date]) }

Max Stage Name: {fixed [Opportunity ID]: sum(if [Max Date] = [Snapshot Date] then [Stage Name] end) }

2nd to Last Date: {fixed [Opportunity ID]: max(if [Snapshot Date] != [Max Date] then [Snapshot Date] end )}

2d to Last Stage Name: {fixed [Opportunity ID]: sum(if [2nd to Last Date] = [Snapshot Date] then [Stage Name] end) }

But for this, you are are going to to have to change the data type of stage name to a number format. Using max on a string type wouldn't yield correct results. Finally drag both stage name calcs to dimensions (if you want to) drop them appropriately next to each other on rows.

final Result:

1 of 1 people found this helpful
• ###### 7. Re: Second to last value by two fields

Thank you!  I'm still testing to see if it's behaving as expected on this huge dataset, but so far so good.

Can you help me understand the != part?

I'm assuming it's if snapshot date is less than max date, then snapshot date?  but if so, why not use <?

2nd to Last Date: {fixed [Opportunity ID]: max(if [Snapshot Date] != [Max Date] then [Snapshot Date] end )}

• ###### 8. Re: Second to last value by two fields

No it means (not equal to). So what I did was to say that if [Snapshot Date] != [Max Date] then [Snapshot Date]. For ID 5 that will give me 2 dates 1/14/2016 and 1/21/2016. And then I said, fixing it at an opportunity ID level, give me the max of those 2 dates which can be called as the second highest date. Makes sense?

You could do less than also, that will still give you the same result I assume.

• ###### 9. Re: Second to last value by two fields

Great!  Thank you so much!  It's taking a long time to test this, but I'm sure it'll work out.

• ###### 10. Re: Second to last value by two fields

I'm going through the testing, and it's not exactly as I expected.  How would I tweak the below item to be previous date, rather than 2nd to last?  That way, per opportunity id, i will have a field of whatever the previous snapshotted stage name was?

2nd to Last Date: {fixed [Opportunity ID]: max(if [Snapshot Date] != [Max Date] then [Snapshot Date] end )}