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

    J Frueh

      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 DateOpportunity IDStage Name
      1/14/20161295%
      1/21/2016580%
      1/28/20162065%
      2/4/20163550%
      1/14/2016575%
      1/21/20162035%
      1/28/20163525%
      2/4/20161215%
      1/14/20162025%
      1/21/20163595%
      1/28/20161265%
      2/4/2016550%

       

      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 DateOpportunity IDStage NamePrevious Stage Name
      1/14/20161295%NULL
      1/21/2016580%75%
      1/28/20162065%25%
      2/4/20163550%25%
      1/14/2016575%NULL
      1/21/20162035%25%
      1/28/20163525%95%
      2/4/20161215%65%
      1/14/20162025%NULL
      1/21/20163595%NULL
      1/28/20161265%95%
      2/4/2016550%80%

       

      Please hook me up with some Tableau magic goodness.

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

          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
            J Frueh

            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
              J Frueh

              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
                pooja.gandhi

                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
                  J Frueh

                  Thank you again for your reply.

                   

                  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
                    pooja.gandhi

                    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
                      J Frueh

                      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
                        pooja.gandhi

                        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
                          J Frueh

                          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
                            J Frueh

                            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 )}