4 Replies Latest reply on Sep 22, 2018 1:28 AM by Yong-Yeow Ching

# WINDOW_MAX with IF Statement Expression

Hi all,

I have a question with regards to the calculation of the Top 10 Tableau Table Calculations | Tableau Software (Table Calculations for Advanced Analysis in Tableau ) for the " Percent change from a reference date" I cannot figure out how the value is derived from a calculated field (i.e. "Adj Close on Reference Date") '

Basically, when I entered the Reference Date as "17/06/2008", and place my cursor in the graph on the Date "04/04/2008", I notice that the value derived from "Adj Close on Reference Date" is 8.06 (refer to attached figure and the source data).

Question -

If I look at the algorithm for this calculated field (see below), I don't know how it derived the 8.06 because based on the example I provided above, the value should be NULL and it doesn't make any sense even though the correct value is 8.06 if you will to filter the date in the source csv file.

WINDOW_MAX(

IF (MAX([Date]) = [Enter Reference Date:]) THEN SUM([Adj Close]) ELSE NULL END,

FIRST(),

LAST()

)

//The Max([Date]) in this example will be "17/09/2009"

If you place your cursor in the graph on 04/04/2008 for Sourcefire, the percentage is 76.2% which is derived from "% Difference" calculation field which call the "Adj Close on Reference Date" calculated field.

=1+(6.14-8.06)/8.06 = 76.2%

Regards,

YYC

• ###### 1. Re: WINDOW_MAX with IF Statement Expression

At date of 4/4

if (max(4/4) = 6/17 then 6.14 else null end  ==> Null

At date of 6/16

if (max(6/16) = 6/17 then 8.20 else null end  ==> Null

At date of 6/17

if (max(6/17) = 6/17 then 8.06 else null end  ==> 8.06

At date of 6/18

if (max(6/18) = 6/17 then 7.92 else null end  ==> Null

Window_max of all the date are consist of only one validate value of 8.06 and null for all of rest dates.

==> window_max(null, null, null,,,,,,, 8.06, null, null,,,,,,,,null) = 8.06

==> window_max also shows 8.06 at any point of date.

"max" before date is only the purpose of aggregation, max, min does not matter.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: WINDOW_MAX with IF Statement Expression

Thanks, Shin.

Your explanation is correct and makes sense. Is there a way to mark this question as answered in the forum just want to ensure points are awarded.

1 of 1 people found this helpful
• ###### 3. Re: WINDOW_MAX with IF Statement Expression

Hi Yong-Yeow

You are welcome and Not form inbox but from origsinsl post, please mark my answer as correct to close the the thread.

Thanks,

Shin