This is the second post on Table Calculations in Tableau. The first post covered the Groundwork, pill types, a worksheet layout, and the basics of the TOTAL() function. This post will look at what I consider the functions: INDEX(), FIRST(), LAST(), and SIZE(), partitioning vs. addressing, and the advanced compute using dialog. I think of the table calculations functions in the following groups:
– Total – The TOTAL() function is unique becuase it is a seperate query to the data source. It works very similar to Subtotals and Grand Totals. It is not an aggregrate of an aggregate, it is a seperete query. It is not concerned about the addressing sort order.
– Order – The functions INDEX(), FIRST(), LAST() are simular functions that compute the order of the marks within a partition. INDEX() is like a ranking function, while FIRST() and LAST() tell you the number of marks to reach the first or last mark in the partition. Sort order fo the addressing is or course import to these functionsSize – The SIZE() function counts the number of marks in a partition. SIZE() is equal to the maximum INDEX() value. The addressing sort order has no effect.
– Reference – The LOOKUP() function references an aggregration in a mark based on an offset, and the PREVIOUS_VALUE() self references this formulas previous result where its argument is the starting previous value. Both are dependant on the sort order of the addressing fields.
– Window – The WINDOW_ functions are an aggregrate of an aggregrate, and have the option to specify a start and end based on either the current first or last mark in the partition. If you do not specify the start/end arguments, then the sort order of the addressing has no impact.
– Running – The RUNNING_ functions are also an aggregrate of an aggregrate, but do not have the start/end options like the WINDOW_ functions. The sort order of the addressing affects it.