4 Replies Latest reply on Mar 14, 2016 6:46 AM by Bas Groothedde

    Window_Min Explanation Formula

    Bas Groothedde

      Hey everyone,


      I am working on jump plots. And currently I struggle in understanding the formula of time_elapse of the jump plot from the following website:  Jump Plot | A new way to visualize event data . I think it is not necessary to understand how jump plots in order to understand the entire formula used.


      ABS(DATEDIFF('day',WINDOW_MIN(MIN([Event Date]),LAST(),LAST()),WINDOW_MIN(MIN([Event Date]),FIRST(),FIRST())


      I understand the functioning of the absolute (ABS) function and of the DATEDIFF (which simply subtracts the first date from the second).
      However, what I do not understand is how Window_min works:

      • What is the effect of taking the minimum of an expression:  MIN([Event Date]
      • Window_Min returns the minimum value of an expression. Why are LAST(), LAST() and FIRST(), FIRST() used as starting as well as ending points. then I am not using a list but can use the expression MIN(Event Date)

      I would be very grateful if anyone could help me with this formula!!!!!


      PS: For more detail on the data used:  Dropbox - Manual ETL for basic Jump Plots - v2015-11-16.xlsx

        • 1. Re: Window_Min Explanation Formula
          Carl Slifer

          Howdy Bas,


          The WINDOW functions are table calculations as such their scope and direction can be assigned. You could look up just within a pane or an individual partition, or you could look up the entire table. It looks at the results already on the table, so the aggregated results not the underlying data.  If its a WINDOW_MIN function and you've set it up to work as table across it will look at all values in the table going across and return the minimum value it finds for the entire table. I've attached a workbook for this example



          IF you open up this function and see how it is written: Table Calculation Functions , you will see that the Start and End for the Window is optional, it is in square brackets. By default it will look up everything you've said to look at in terms of scope and direction. But choosing first() and last() you've set it to use the default just more fancily. You are able to say things like FIRST()+1 and LAST()-1 which would look at everything from the second to the second to last in the partition. The workbook attached has a more complicated usage of this a well.


          Without seeing your data LAST() to LAST() doesn't make much since to me unless they no matter what only want the last value in the partition to be used. Your calculation seems to be taking the value in the first part of the partition and the the value in the last part of the partition Such that if you were looking at table across and you had 4 columns you would be looking at the first column and the last column. And then subtracting the last column from the first column. But returning the ABS value so the order doesn't matter in this case.



          Carl Slifer


          1 of 1 people found this helpful
          • 2. Re: Window_Min Explanation Formula
            Bas Groothedde

            Thank you very much Carl.


            There is only one question remaining: Why would you use MIN[event date]. Then you take the minimum of the expression and no matter what the start and end date is it is always the minimum of the partition. And if I subtract, as seen in the case above, from another minimum value than this must equal 0 right?

            • 3. Re: Window_Min Explanation Formula
              Carl Slifer

              Hi Bas,


              Good question and I should have been more clear. In order to use a table calculation such as the WINDOW() calcs your data needs to be aggregated.In this case it is pulling the minimum value for the underlying data source would be aggregating it.


              "Then you take the minimum of the expression and no matter what the start and end date is it is always the minimum of the partition. And if I subtract, as seen in the case above, from another minimum value than this must equal 0 right?"

              Not quite. you take the minimum of the underlying data and then take the window_minimum with respect to the start and end date allowed that are in that partition.


              I suspect that whoever had this formula first has only one value instead of underlying data for each cell





              If we assume our data came in and we built a crosstab with the name as rows and year as columns we get this. If we had 3 values that made up Bob's 2011 when we took the MIN[Value]) we would get the smallest value there. However I'd suspect there was not underlying data and it came in at this level of aggregation already. If that;s the case taking the MIN[Value]) of a single value will return that value.  Hence your formula in this case looking just at Bob.


              ABS(DATEDIFF('day',WINDOW_MIN(MIN([Event Date]),LAST(),LAST()),WINDOW_MIN(MIN([Event Date]),FIRST(),FIRST())

              Finds the minimum event data, in this case its a number value I used for an example. It returns 58, 82, 76, and 55. going across the table. And then the WINDOW_MIN only looks at the first column of the partition and returns 58, not because its the smallest in the entire partition but because its the smallest that it was told to use because of the FIRST() and FIRST() start and ending points,


              ABS(DATEDIFF('day',WINDOW_MIN(MIN([Event Date]),LAST(),LAST()),WINDOW_MIN(MIN([Event Date]),FIRST(),FIRST())

              This again returns 58, 82, 76, 55 but the WINDOW_MIN is restricted to using the last column hence it returns 55.


              When taking the datediff (lets assume these are dates) it will take the difference in days between  the first value and the second value or 58 - 55. = 3.


              Table Calculations are notoriously difficult to assist with because of how vastly different they can be used as tricks to get around pre-aggregated data (at times) to actually get the minimum of underlying data and mostly because of scope and direction mattering so much. Past this a sample workbook will need to be provided. Sorry!



              Carl Slifer


              1 of 1 people found this helpful
              • 4. Re: Window_Min Explanation Formula
                Bas Groothedde

                Great Carl!
                Thank you so much!