3 Replies Latest reply on Apr 12, 2018 6:35 PM by swaroop.gantela

    How can I use an advanced formula

    AGUSTIN ROSA

      I would like to create a formula that check if 6 days or more one variable is above 30, if that it's TRUE I want to receive a table with those values. PS: I have a table from the last 5000 days with information of the weather, I do normally everything manually with Excel, but I want to automatizate it with Tableau,

       

      Thanks,

        • 1. Re: How can I use an advanced formula
          Sankarmagesh Rajan

          Hi Agustin,

           

          Please use the function available in tableau.

           

          for ex:

          want to get last 7 days or more days please create last n days parameter with all.

          create  calculation

          last()<[last n days]

          once calculation over then drag to filter.

           

          you will get parameter selected days in view.

           

          Refer

          Tableau Tip: Default a date filter to the last N days

           

          Thanks

          sankar

          • 2. Re: How can I use an advanced formula
            Eric Hammond

            Hi Agustin,

             

            I found this to be an interesting challenge but then failed to find a smart way go about it.  Here is a long way to show all dates that are part of a consecutive six-day stretch with temperatures over a given level.  Create a calculated field following the pattern below, then just use that field as a filter.  The worksheet will need to have dates at the day level for this to work as desired.

             

            IF      Lookup(Avg([Temperature]),0) > [Temp Param]

                AND Lookup(Avg([Temperature]),-5) > [Temp Param]

                AND Lookup(Avg([Temperature]),-4) > [Temp Param]

                AND Lookup(Avg([Temperature]),-3) > [Temp Param]

                AND Lookup(Avg([Temperature]),-2) > [Temp Param]

                AND Lookup(Avg([Temperature]),-1) > [Temp Param] THEN 'Flag'

            ELSEIF  Lookup(Avg([Temperature]),0) > [Temp Param]

                AND Lookup(Avg([Temperature]),1) > [Temp Param]

                AND Lookup(Avg([Temperature]),-4) > [Temp Param]

                AND Lookup(Avg([Temperature]),-3) > [Temp Param]

                AND Lookup(Avg([Temperature]),-2) > [Temp Param]

                AND Lookup(Avg([Temperature]),-1) > [Temp Param] THEN 'Flag'

            ELSEIF  Lookup(Avg([Temperature]),0) > [Temp Param]

                AND Lookup(Avg([Temperature]),1) > [Temp Param]

                AND Lookup(Avg([Temperature]),2) > [Temp Param]

                AND Lookup(Avg([Temperature]),-3) > [Temp Param]

                AND Lookup(Avg([Temperature]),-2) > [Temp Param]

                AND Lookup(Avg([Temperature]),-1) > [Temp Param] THEN 'Flag'

            ELSEIF  Lookup(Avg([Temperature]),0) > [Temp Param]

                AND Lookup(Avg([Temperature]),1) > [Temp Param]

                AND Lookup(Avg([Temperature]),2) > [Temp Param]

                AND Lookup(Avg([Temperature]),3) > [Temp Param]

                AND Lookup(Avg([Temperature]),-2) > [Temp Param]

                AND Lookup(Avg([Temperature]),-1) > [Temp Param] THEN 'Flag'

            ELSEIF  Lookup(Avg([Temperature]),0) > [Temp Param]

                AND Lookup(Avg([Temperature]),1) > [Temp Param]

                AND Lookup(Avg([Temperature]),2) > [Temp Param]

                AND Lookup(Avg([Temperature]),3) > [Temp Param]

                AND Lookup(Avg([Temperature]),4) > [Temp Param]

                AND Lookup(Avg([Temperature]),-1) > [Temp Param] THEN 'Flag'

            ELSEIF  Lookup(Avg([Temperature]),0) > [Temp Param]

                AND Lookup(Avg([Temperature]),1) > [Temp Param]

                AND Lookup(Avg([Temperature]),2) > [Temp Param]

                AND Lookup(Avg([Temperature]),3) > [Temp Param]

                AND Lookup(Avg([Temperature]),4) > [Temp Param]

                AND Lookup(Avg([Temperature]),5) > [Temp Param] THEN 'Flag'

            END

            • 3. Re: How can I use an advanced formula
              swaroop.gantela

              Agustin,

               

              Here is an additional version of what Eric developed.

               

              Flag every point above 30:

              IF SUM([Quantity])>30 THEN 1 ELSE 0 END

               

              Sum up flags in a window of 6, and

              Mark first point in a sequence of 6 in a row above 30:

              IF WINDOW_SUM([Flag > 30],0,5)=6 THEN 1 ELSE 0 END

               

              Lookback to see if six points back was the start of a sequence:

              IF [Flags over Window]=1

              OR LOOKUP([Flags over Window],-1)=1

              OR LOOKUP([Flags over Window],-2)=1

              OR LOOKUP([Flags over Window],-3)=1

              OR LOOKUP([Flags over Window],-4)=1

              OR LOOKUP([Flags over Window],-5)=1

              THEN 1 ELSE 0 END

               

              As Eric said, the last calculation can be used a filter.

               

              Please see workbook attached in the Forum Thread.