13 Replies Latest reply on Jan 4, 2019 1:43 PM by ChicagoTableauUser

    Formula help

    ChicagoTableauUser

      I have a formula that gives me the latest status value from the audit trail (see below)

      I now need to create another similar formula that gives the latest status value prior to a particular date so the max date prior to a particular date.

      Is that possible?

       

      Thanks for any help!

       

       

      { FIXED [Opportunity]:

      max(

      IF

      //max date

      ({ FIXED [Opportunity],[Property]:

                max(

                  DATETIME(

                  ([Date (Opportunity AuditTrail)])

                 )

                )

      })

      =[Date (Opportunity AuditTrail)]

       

      then

         if [Property]="Status" then

          [New Value] end end )}

        • 1. Re: Formula help
          Jim Dehner

          I don't have a dataset that mimics what you are trying to do but I can step you through a concept that would provide direction

          I noted you have a embedded an lod that returns a max data by opportunity and property - (BTW embedding the formula will make your sheet run slower - each record is checked individually and yo are recalculating the embedded formula)

           

          so here's a thought

          make an LOD calculation that returns the date by opportunity and property (all of them not just the max)

          then create a second calculation to get the Max date based on the LOD

          then you can use that max calc against opportunity audit trail date as you did above and use lookup(   ,-1) to get the previous date

           

           

          Just a thought

          Jim

          1 of 1 people found this helpful
          • 2. Re: Formula help
            ChicagoTableauUser

            Thanks Jim, can you help with the formula to pull all dates not just the max? I have the below but Im not able to just remove the "Max"

             

             

            ({ FIXED [Opportunity],[Property]:

            max(if [Property]="Status" then

                      (

                        DATETIME(

                        ([Date (Opportunity AuditTrail)])

                       )

                      )

            end)})

            • 3. Re: Formula help
              Jim Dehner

              here is what you are trying to do -

              you want to create a virtual layer in your data that is at a level above the base data - (thats what fixed lod's do)

              but you want to create it in a way that the "MAX" record will return the value you want AND the record (with the same combination of dimensions) that precedes it will return the next to last value

               

              Fixed will create combinations of the dimensions that precede the colon - in your case property and opportunity (not clear where "Status" comes in - are the multiple Opportunity Dates that are related to Property = status and you want the last and next to last OR is there only one Record with property = status and you want the record before that regardless of what property equals)

               

              not easy to do this without seeing the data - even dummy data would help

              in any event when creating the LOD tableau will look at each combination of property and opportunity (and maybe something else like date) and then aggregate what follows the colon by the aggregating function - if there are multiple records with the same combination of the dimensions that preceded the colon and you aggregate by max then you get the one record with the max value - if there is only a single record you get that single record value if you use max or min makes no difference

              so what - if there are multiple records with property =status but they have different dates then include the data as a dimension that precedes the colon and use max

              if there is only one record that for each "property whatever' and you want to get the record before status regardless of that value then you would want the max date withproperty = status and the max date that preceded it regardless -

               

              Jim

              • 4. Re: Formula help
                Jim Dehner

                BTW if you send me the book with some dummy data it may be much easier based on the data

                I am trying to avoid table calculations so you can use the value on different sheets without rebuilding a table

                 

                Jim

                • 5. Re: Formula help
                  ChicagoTableauUser

                  Hi Jim,

                   

                  I have attached a test workbook that hopefully demonstrates what im trying to do. My real data source doesn't include the MAX Status or the Old Value columns and thats what im trying to achieve with a calculation.

                   

                  Thanks

                  • 6. Re: Formula help
                    Jim Dehner

                    see the attached

                     

                    there are 2 tabs the first uses lookup - a table calc

                    each uses this to find the max date for status

                     

                     

                    the lookup version then uses this

                    and returns this

                     

                    it means you need a table - the second uses LODs

                    after finding the max determine the date difference between the max and each record

                    then the min > 0 (the next to last)

                    then the date

                    not table calcs but in table form it looks like this

                     

                    if you just want the value it looks like this

                     

                     

                    Jim

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                    1 of 1 people found this helpful
                    • 7. Re: Formula help
                      ChicagoTableauUser

                      Thanks Jim, really appreciate the help.

                      For the next to last date I need it to be relative to the "date" I have outlined below in the table.

                       

                      Thanks

                      • 8. Re: Formula help
                        Jim Dehner

                        please decode the accounting speak into something that is directly related to the data set

                        thanks

                        Jim

                        1 of 1 people found this helpful
                        • 10. Re: Formula help
                          Jim Dehner

                          doesn't help why 9/4 you wanted the next to last date?  what is nte data triggers 9/4 v 12/31 and you only wanted project = 'Status"?

                          1 of 1 people found this helpful
                          • 11. Re: Formula help
                            ChicagoTableauUser

                            Hi Jim, apologies for the confusion. Ultimately my goal is to add the "Old Value" column per below. My understanding was that I would need to calculate the date piece first but maybe not.

                             

                            • 12. Re: Formula help
                              Jim Dehner

                              see the attached

                              I can't do it without a lookup function

                               

                              I have 2 final calculations to show you how do get the final table you want

                              the first includes some nulls - the second removes the nulls

                               

                              first the max value in your table by opportunity

                              this will return the old value

                              set like this

                               

                              it returns nulls for the first record in each opportunity

                              to get rid of the nulls I added this calculation

                              set like this

                               

                              you can hide any column in the table  but you get this

                              Jim

                              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                              1 of 1 people found this helpful
                              • 13. Re: Formula help
                                ChicagoTableauUser

                                Thank you Jim!