Person: Typically in Looker, data explorers are given access to one or more explorers predefined by LookML developers. At your own company, that would likely mean your data and analytics team, data engineers or data analysts, but sometimes you'll need particular logic for which they haven't provided a dimension or measure, maybe because you have a new kind of question or use case. This is when you might need a table calculation. Table calculations are used to define what we call on-the-fly metrics because they run on your query results instead of your whole database. You'll need to start by generating results with at least one dimension or measure, and then you can incorporate these fields into spreadsheet software like formulas to calculate a new metric. With table calculations, you can also set up a custom field that isn't included in the setup dimensions and measures provided by your LookML developers. For example, you can use a table calculation to create a new measure for percent of flights canceled from existing measures on number of flights completed and number of flights canceled. Table calculations are really cool and convenient. However, because they run after the query has been set up and are written on the fly, they can introduce differences across an organization, and they need to be recreated manually if they are not saved to a Look or a dashboard. So we recommend using table calculations for a couple of specific use cases. The first is to prototype a field or query, like if you want to validate whether it's useful enough to reuse. Similarly, if you need to answer a one-off question, then a table calculation is useful. If you find yourself needing the same logic repeatedly, you should work with your LookML developer team to have that logic added to the data model as dimensions or measures that everybody can use. Another scenario that might call for a table calculation is if you need to create something based purely on the result set and not on the overall data. A specific example would be if you want to create a kind of visualization like one not available in your current Looker version, or for what you don't have the right types of fields in your data model. In this case, you can use a table calculation to translate your data or visualization into the form you want. Now, to understand how table calculations are created, let's walk through an example. Since table calculations only operate on the results of your Explore query, you first need to bring in one or more dimensions or measures by selecting the fields you need to calculate the desired metric and then click run. For example, imagine that you want to calculate the percent of flights canceled over a period of time. You can start by selecting the dimension for depart date and the measure for flight count and canceled count and clicking run. After you select the dimensions and measures and run the initial results, you can now create a table calculation from the results. Next to Custom Fields, click the add button and select table calculation. A new popover window will appear in which to enter your formula. Now, one thing to be aware of here is that because administrators must enable this feature for any uses, you might not have the Custom Fields view in the Explorer of your organizations Looker instance, even if you do have permission to use table calculations. In that case, you would see a calculations button at the top of the data table. Assuming Custom Fields are available to you, in the table calculation popover you will need to select a calculation type or leave the selected option for custom expression to write ad hoc expressions. Next, give your new table calculation a name such as Percent of Flights Canceled. This [Indistinct] will show up as the column header and in the legend of the visualization. If using a custom expression for the table calculation, enter the formula you'd like to apply. For example, you can calculate the percentage of canceled flights by dividing the canceled flight count by the overall count of flights. Note that Looker strives for parity with commonly-used spreadsheet software, so if you simply type the letter A, you can see all the various functions that Looker supports. You can get the absolutely value of a number, add time intervals to a date, can calculate multiple values, write if conditions and so on. If you want to see the full list of supported functions and some more information about them, you can click on the help plus syntax reference icon above the top right of the window, which takes you to our documentation. An important thing for people very familiar with spreadsheet software to know is that with Looker table calculations you do not start the formula with an equal sign. That might feel a bit weird, but you'll get used to it. Next, specify the format of the results you'd like, decimal, percent and so on. Finally, click Save to save your table calculation. You will now see table calculations appear in your data table in green. The values will show up right away since you have already run your query with the dimensions and measures. Table calculations also get displayed in the visualization. If you don't want a specific field or table calculation in your visualization, you can click on the gear icon in the column header and select Hide From Visualization. More often than not, the table calculation is the thing you actually want to visualize. It might be the foundational dimensions, such as flight count, because you want the visualization to show only the percent of canceled flights. An icon of an eye with a line crossing over it will appear to indicate that the column is hidden. So here's how the final line chart would look if you keep the dimension and the table calculation but hide the two measures used in the table calculation formula. In addition to writing custom expressions for table calculation, you can choose from some predefined options such as calculating the percent of column total, percent change from a previous row, running totals and more. With options for both predefined and custom expressions, Looker gives you the power to customize your visualizations and reports as needed. In summary, table calculations allow you to instantaneously create new metrics and fields without needing to wait for a LookML developer to create them for you. Enjoy using table calculations. Just remember that since they run after the query has been set up and are written on the fly, they can introduce differences across an organization, and they need to be recreated manually if they're not saved to a Looker or dashboard. For these reasons, table calculations are most useful for prototyping new metrics, entering one-off questions or creating visualizations based purely on query results. And with this knowledge, we hope you now look forward to playing around with table calculations in Looker.
