3 Replies Latest reply on May 2, 2017 11:04 AM by Elliott Stam

    LOD calculation.

    Salimah Brooks

      The bolded statement needs some tweaking. I need it to run for Friday's data.  Can some assist me please?

       

      I have the below parameters set up based on what date the user selects.  If they don't select a custom date, the report should default to yesterdays date. It works for every other day except for Monday. On Monday's the report should give me Friday's data.

       

      IF [Date Selection Parameter] = 1 AND [Day of Week (copy)] = 3

      THEN

      (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

       

       

      ELSEIF [Date Selection Parameter] = 1 AND [Day of Week (copy)] = 4

      THEN

      (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

       

       

      ELSEIF [Date Selection Parameter] = 1 AND [Day of Week (copy)] = 5

      THEN

      (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

       

       

      ELSEIF [Date Selection Parameter] = 1 AND [Day of Week (copy)] = 6

      THEN

      (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

       

       

      ELSEIF [Date Selection Parameter] = 1 AND DATEPART('weekday', today()) = 2

      THEN

      (if [New Key Time (Central Time)]-3 >{FIXED: MAX([New Key Time (Central Time)])} and [New Key Time (Central Time)]-2 < {FIXED: MAX([New Key Time (Central Time)])} then 1 end)

       

       

       

       

      ELSEIF [Date Selection Parameter] =  0

      THEN

      (if [New Key Time (Central Time)] >= [Start Date] and

      [New Key Time (Central Time)] <= [End Date] then 1 end)

      END

        • 1. Re: LOD calculation.
          Elliott Stam

          Hi Salimah,

           

          Could you please post the file as .twbx instead of .twb? I'm happy to help you figure this out but without the packaged workbook I will continue to receive this error:

           

          Elliott Stam - InterWorks

          • 2. Re: LOD calculation.
            Salimah Brooks

            Hi Elliott,

            Here you go.

            • 3. Re: LOD calculation.
              Elliott Stam

              Hi Salimah,

               

              The .twbx was published as a live connection, so unfortunately I am not able to interact with the workbook (I am prompted for your SQL credentials). If you were to create an extract and then save as a .twbx, that would work, however after seeing how many fields are in your workbook I would strongly recommend you consider whether or not there is sensitive data inside that you do not want to expose to the public.

               

              That said, I was able to see the formulas you are using and I took a blind stab at patching up your LOD calculation formula.

               

              I changed the formula to reference the day of week, rather than a number, for two reasons:

              1) It makes the formula more readable

              2) I used Tableau's Superstore data to test the 'whole number' conversion of DATENAME('weekday', [Date]) and found that comparing that to 1, 2, 3, etc. resulted in null values... however, comparing that same function output to "Monday", "Tuesday", etc. produced the expected true/false outcome.

               

              Finally, in your "Monday" portion of the logic I encapsulated each portion (X and Y) of the "if X and Y then..." logic statement in parentheses to make sure the statement was evaluated as desired.

               

              I'm not sure that this will work for you, since I could not actually test the results in your workbook (again, no access to the underlying data) but perhaps this will work.

               

              Here is the same formula, but as text so you can copy/paste and test it.

               

              IF [Date Selection Parameter] = 1 AND [Day of Week] = "Tuesday"

              THEN

              (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

               

              ELSEIF [Date Selection Parameter] = 1 AND [Day of Week] = "Wednesday"

              THEN

              (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

               

              ELSEIF [Date Selection Parameter] = 1 AND [Day of Week] = "Thursday"

              THEN

              (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

               

              ELSEIF [Date Selection Parameter] = 1 AND [Day of Week] = "Friday"

              THEN

              (if [New Key Time (Central Time)]>= {FIXED: MAX([New Key Time (Central Time)])}-1 then 1 end)

               

              ELSEIF [Date Selection Parameter] = 1 AND [Day of Week] = "Monday"

              THEN

              (if ([New Key Time (Central Time)]-3 >= {FIXED: MAX([New Key Time (Central Time)])}) and ([New Key Time (Central Time)]-2 < {FIXED: MAX([New Key Time (Central Time)])}) then 1 end)

               

              ELSEIF [Date Selection Parameter] =  0

              THEN

              (if [New Key Time (Central Time)] >= [Start Date] and

              [New Key Time (Central Time)] <= [End Date] then 1 end)

              END

               

              Hope this helps,

               

              Elliott Stam - InterWorks