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

    WINDOW_MAX with IF Statement Expression

    Yong-Yeow Ching

      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.

       

      Adj Close on Reference Date

      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%

       

       

      Please help. Thanks.

       

      Regards,

      YYC