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

# How can I use an advanced formula

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

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

Thanks

sankar

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

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

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.