
1. Re: Window_Min Explanation Formula
Carl Slifer Mar 14, 2016 5:22 AM (in response to Bas Groothedde)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.
Cheers!

Window Functions.twbx 1.5 MB


2. Re: Window_Min Explanation Formula
Bas Groothedde Mar 14, 2016 5:51 AM (in response to Carl Slifer)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 Mar 14, 2016 6:20 AM (in response to Bas Groothedde)1 of 1 people found this helpfulHi 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
Name 2011 2012 2013 2014 Bob 58 82 76 55 Jim 37 94 97 22 Tony 19 86 70 34 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 preaggregated 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!
Cheers
Carl Slifer
InterWorks

4. Re: Window_Min Explanation Formula
Bas Groothedde Mar 14, 2016 6:46 AM (in response to Carl Slifer)Great Carl!
Thank you so much!