Typically in Looker Data Explorers are given access to one or more explores 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. 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 set of 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 your Look or dashboard. We recommend using table calculations for a couple of specific use cases. The first is to prototype a field or query 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 visualization like one not available in your current Looker version, or for which 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 explored query, you first need to bring in one or more dimensions or measures by selecting the fields you need to calculate the desired metric. 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 pop-over 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 end-users, 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 pop-over, you will need to select a calculation type, or leave this selected option for custom expression to write adhoc expressions. Next, give your new table calculation a name, such as percent of flights canceled. The same will show up as the column header and in the region of the visualization. If you're 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 counter flights. Note that Looker stars for parody with commonly used spreadsheet software. If you simply type the letter a, you can see all the various functions that Looker supports. You can get the absolute value of a number, add time intervals to a date, concatenate 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 helpless 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 or measures that you don't want, 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. 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 calculations, you can choose from some predefined options such as calculating the percent of column total, present 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 setup 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 Looker dashboard. For these reasons, table calculations are most useful for prototyping new metrics, answering one-off questions, or creating visualizations based purely on query results. With this knowledge, we hope you now look forward to playing around with table calculations in Looker.
