5 Replies Latest reply on Oct 31, 2018 3:17 PM by Jhobe Steadman

    Last Date a measure had a given value

    Jhobe Steadman

      I have a measure which is either 0 or a value defined by an adjustable parameter for each day in a 3 year time span.  I'd like to determine the date of the last occurrence of the parameter value (when it wasn't 0).  I've tried various combinations of using index(), max(date), if clauses, etc. and haven't figured it out.  Suggestions please?  Thank you

        • 1. Re: Last Date a measure had a given value
          venkatram

          Create sample file like below,

           

           

          Drop Cacl3 in the filter and unselect all the date,and apply latest date.

           

           

          Final results:

           

          Link for your reference.

           

          https://public.tableau.com/profile/dataplusscientist#!/vizhome/LatestValue/Sheet7?publish=yes

          • 2. Re: Last Date a measure had a given value
            Jhobe Steadman

            First, thank you very much for your reply, I appreciate it.

            Unfortunately, my “calculation2” is an average so my version of your Calculation2 “IF  = 0 THEN  END” gives an error: “Cannot mix aggregate and non-aggregate comparisons or results in “if” expressions”

             

            I tried the 2-step approach of assigning a # to the “IF  ….” Calculation and then another measure “IF  = # THEN  END” but that gives the same error as above.

             

            If you have a workaround for this problem I would of course appreciate your help.

             

            Best regards

            • 3. Re: Last Date a measure had a given value
              venkatram

              For better understanding your requirement, we need a sample data dump or your working file with .twbx format.

              • 4. Re: Last Date a measure had a given value
                Jhobe Steadman

                I have >1.5M row of data and creating a sample data set would take hours.  Does the following description help (I have substituted placeholder names for the actual variable, measure & parameter names) :

                 

                Dimension: Every date for the last 3 years

                Dimension: Test result for every test (multiple test results per day)

                 

                Measure: #Tests for every date

                Measure “AvgX”: Window_Avg(Sum(Test results)) for every date

                Measure “AvgY”: Window_Avg(Sum(#Tests)) for every date

                 

                The following measures:

                ExcludeDay

                IF  >=  AND  >  THEN  END             (Note: I can’t put a date in where ParamZ is or I get the “cannot mix aggregate and non-aggregate comparisons or results in “if” expressions” error)

                 

                LastExcludeDay

                IF  =  THEN Max() END                           (This  still gives the error above.)

                 

                Again, the goal is to get the date of the last time that  equaled ParamZ, preferably relative to a date that I put in another parameter

                 

                Thank you

                • 5. Re: Last Date a measure had a given value
                  Jhobe Steadman

                  Apparently, any of my text that has square brackets around it is being lost so I am resending my reply with all square brackets removed.

                   

                   

                  I have >1.5M row of data and creating a sample data set would take hours.  Does the following description help (I have substituted placeholder names for the actual variable, measure & parameter names) :

                   

                  Dimension: Every date for the last 3 years

                  Dimension: Test result for every test (multiple test results per day)

                   

                  Measure: #Tests for every date

                  Measure “AvgX”: Window_Avg(Sum(Test results)) for every date

                  Measure “AvgY”: Window_Avg(Sum(#Tests)) for every date

                   

                  The following measures:

                  ExcludeDay

                  IF AvgX >= ParamX AND AvgY > ParamY THEN ParamZ END             (Note: I can’t put a date in where ParamZ is or I get the “cannot mix aggregate and non-aggregate comparisons or results in “if” expressions” error)

                   

                  LastExcludeDay

                  IF ExcludeDay = ParamZ THEN Max(Date) END                           (This  still gives the error above.)

                   

                  Again, the goal is to get the date of the last time that AvgX equaled ParamZ, preferably relative to a date that I put in another parameter

                   

                  Thank you