Mẹo Hướng dẫn Which functions perform a calculation on the values of a column from selected rows group of answer choices? Mới Nhất
Dương Anh Tuấn đang tìm kiếm từ khóa Which functions perform a calculation on the values of a column from selected rows group of answer choices? được Update vào lúc : 2022-08-29 14:34:02 . Với phương châm chia sẻ Mẹo về trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi đọc Post vẫn ko hiểu thì hoàn toàn có thể lại Comment ở cuối bài để Ad lý giải và hướng dẫn lại nha.Table calculations make it easy to create ad hoc metrics. They are similar to formulas that are found in spreadsheet tools such as Excel. Table calculations appear as green columns in the data table, rather than as blue columns (dimensions) or orange columns (measures).
Nội dung chính- Table calculations are different from LookML-defined fields and custom fieldsCreating table calculationsUsing the Custom Fields section of the field picker to create table calculationsUsing the Data bar to create table calculationsShortcuts for common calculationsTypes of common calculation shortcutsUsing the Edit table calculation pop-up to use shortcuts for common calculationsDuplicating table calculationsEditing table calculationsDeleting
table calculationsSorting table calculationsWhen table calculations cannot be sortedCalculations that hit a row limitSorting a dimension
or measure after sorting a table calculationCalculations that use an offset function cannot be sortedUsing table calculations in visualizationsConsiderations for using table calculationsWhen coding a definition for a column that will contain a high percentage of null values What attribute can you use to optimize the storage?What button do you click to see the results of a query?What is a type of field that displays the result of an expression?Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?
The last column in the following table uses a table calculation to combine three fields in the data using the concat function.
Table calculations can perform mathematical, logical (true/false), lexical (text-based), and date-based calculations on the dimensions, measures, and other table calculations in your query. The formulas that you use to execute these calculations are called Looker expressions (Lexp).
Table calculations are different from LookML-defined fields and custom fields
There are a few differences between table calculations and LookML-defined fields defined in LookML:
- Table calculations give anyone who has the appropriate permissions the ability to create calculations that are based on LookML-defined fields, rather than the ability to create LookML-defined fields, which require that the user have development permissions
and understand LookML.Table calculations operate on the results from your query after it has run, as opposed to LookML-defined or custom fields, which are part of the query that is run against the database. In other words, first you select a set of dimensions and measures and run your report as normal, and then you can base table calculations on the data in that report.Although table calculations are simpler and quicker to create than LookML-defined fields,
they are not as easily controlled as LookML-defined fields. Since any user can create a table calculation, they might not be the "official" calculations. Keep this trade-off in mind as you decide between LookML-defined fields and table calculations, since Looker leverages LookML to maintain a single source of truth.
There are a few differences between table calculations and custom fields:
- Custom fields generate SQL that will run against the database, similar to a LookML-defined field. Table calculations are executed post-query and do not run against the database.Table calculations are dependent on data from the data table; custom fields are not.
Table calculation fields appear next to dimensions and measures in the table. If you want to reuse your table calculations in the future, be sure to save your Explore as a Look or as a dashboard tile.
Creating table calculations
To allow users or groups to create table calculations, your Looker admin must give those users or groups access to the feature by granting them the create_table_calculations permission.
Looker's Explore page has a built-in Looker expression editor to help you create table calculations, custom fields, and custom filters. If you are a Looker developer who is creating a data test to verify the logic of your model, you can also use the Looker expression editor to build a Looker expression, then copy the expression into your data test's expression parameter.
Before you create a table calculation, make sure that all the fields you want to use in the table calculation have been selected from the field picker and that you have run the query.
There are two ways to access the Looker expression editor from the Explore page:
- Using a calculation's three-dot Options menu in the Custom Fields section of the field pickerUsing the Data bar (the only way to create
table calculations if your instance is not enabled for custom fields)
Using the Custom Fields section of the field picker to create table calculations
If your Looker instance is enabled for custom fields and you have the permissions to create table calculations, you can use the the Custom Fields section of the field picker to open the Edit table calculation pop-up:
Open the Custom Fields section of the field picker.Select Add.Select Table Calculation to open the Edit table calculation pop-up.
Then, for each table calculation:
Select a calculation type from the Calculation drop-down. The options for a Custom expression display by default.
Add the calculation definition, including selecting a shortcut calculation's Source column, as desired. Only numeric fields that appear in the Explore's data table are eligible for calculation types other than Custom expression.
- If Custom expression is selected from the Calculation drop-down, enter a Looker expression in the large text box to create your calculation. You can only create table calculations from fields that appear in
the Explore's data table. Looker expressions can be quite simple; or they can use as many fields, functions, and operators as your business logic requires. The expression you create can evaluate to a number, date, string (text), or Boolean (true/false).The Creating Looker expressions documentation page explains how to create Looker expressions and how the editor can assist you.
Select a format other than the default from the Format drop-down, if desired.
Enter a new calculation name other than the default in the Name field, as desired. The calculation name appears in the field picker and in the data table.
Select + Add Description to add an optional description of up to 255 characters that can give other users more context or information about the table calculation.
Select Save.
The new calculation will automatically appear in the data table and in the Custom Fields section of the field picker. As with other fields, you can select the calculation's name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation.
Using the Data bar to create table calculations
If your Looker instance is not enabled for custom fields, selecting the Add calculation button from the Data bar is the only way to create table calculations:
Then, for each table calculation:
Select a calculation type from the Calculation drop-down. The options for a Custom expression display by default.Add the calculation definition, including selecting a shortcut calculation's Source column, as desired. Only numeric fields that appear in the Explore's data table are eligible for calculation types other than Custom expression.- If Custom expression is selected from the Calculation drop-down, enter a Looker expression in the large text box to create your calculation. You can only create table
calculations from fields that appear in the Explore's data table. Looker expressions can be quite simple; or they can use as many fields, functions, and operators as your business logic requires. The expression you create can evaluate to a number, date, string (text), or Boolean (true/false).The Creating Looker expressions documentation page explains how to create Looker expressions and how
the editor can assist you.
The new calculation will automatically appear in the data table and in the Custom Fields section of the field picker if your instance is enabled for custom fields. As with other fields, you can select the calculation's name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation.
Shortcuts for common calculations
If you have the permissions to create and edit table calculations, you can perform a shortcut calculation on certain numeric fields that are in an Explore's data table — including other table calculations. You cannot create shortcut calculations on custom fields if your instance is enabled for custom fields.
You can perform shortcut calculations in one of the following ways:
- Using a field's data
table gear menuUsing the Edit table calculation pop-up (the only way to delete table calculations if your instance is not enabled for custom fields)
The second method lets you customize the table calculation as you create it, such as renaming, adding an option description, or selecting a different format other than the default.
The following section describes the types of available calculation options.
Types of common calculation shortcuts
Several types of calculations are available as shortcut options. The following table lists each available calculation along with its description, input, and Looker expression (Lexp), and the default value format and default name of the calculation. You can specify a different name or value format by editing your calculation.
For common calculation shortcuts that are only available for pivoted results, see the table of common pivot calculation shortcuts on this page.
The order in which an Explore's data table is sorted can impact calculations that are based on a value's position relative to other values in a column, such as % of previous row, % change from previous row, % of previous column, % change from previous column and % of row. Make sure an Explore's data table is sorted accordingly.
CalculationDescriptionInputLexpFormatName % of column The row value divided by the sum of values in the column. When the row limit has been reached, this calculation includes only values in the data table. field_1 field_1/sum(field_1) % (0 decimal places) Percent of view_name field_name % of previous row The current row's value divided by the value of the row below. field_1 field_1/offset(field_1, 1) % (0 decimal places) Percent of previous - view_name field_name % change from previous row The difference between the current row's value and the value of the row below, divided by the value of the row below. field_1 field_1/offset(field_1, 1) - 1 % (0 decimal places) Percent change from previous - view_name field_name Running column total The cumulative sum of the current row's value and all previous row values in the column. field_1 running_total(field_1) Default formatting Running total of view_name field_name Rank of column The rank of a row's value among all values in the column. When the row limit has been reached, this calculation includes only values in the data table. field_1 rank(field_1,field_1) Default formatting Rank of view_name field_nameWhen Explore results are pivoted, there are more common calculation shortcuts available for pivoted measures.
The following table lists each available calculation along with its description, input, and Looker expression (Lexp), and the default value format and default name of the calculation. You can specify a different name or value format by editing your calculation.
CalculationDescriptionInputLexpFormatName % of previous column For pivoted fields, the current column's value divided by the value of the column to its left. field_1 field_1 / pivot_offset(field_1, -1) % (0 decimal places) Percent of previous column of view_name field_name % change from previous column For pivoted fields, the difference between the current column's value and the value of the column to the left, divided by the value of the column to the left. field_1 (field_1/pivot_offset(field_1, -1)) - 1 % (0 decimal places) Percent change from previous column of view_name field_name % of row For pivoted fields, the percent of the current column's value divided by the row sum of that field. field_1 field_1/sum(pivot_row(field_1)) % (0 decimal places) Percent of row Running row total For pivoted fields, the cumulative sum of the current column and all previous columns in this row. field_1 sum(pivot_offset_list(field_1,-1*pivot_column()
+1,pivot_column())) Default formatting Running row total
If your Looker instance is enabled for custom fields and you have the permissions to create and edit table calculations, you can create a shortcut calculation from a field's data table gear menu with the following steps:
Select the gear menu next to the field's name in the data table. In this example, the user chooses to perform a calculation on the Inventory Items Count measure and selects that field's gear menu.Select Calculations to display the available calculation types.Select a
calculation type. The following example uses % of previous row to compare the count of inventory items with the previous month's count of inventory items.Pivot calculation types will not appear unless Explore results are pivoted.
The new calculation will automatically appear in the data table:
The new calculation will also appear in the Custom Fields section of the field picker. As with other fields, you can select the calculation's name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation, including editing the calculation.
Using the Edit table calculation pop-up to use shortcuts for common calculations
This method lets you select a format or name other than the default, or add a description as you create the calculation.
Open the Edit table calculation pop-up.
- If your instance is enabled for custom fields, you can use either the
Custom Fields section or the Add calculation button from the Data bar.If your instance is not enabled for custom fields,
use the Add calculation button from the Data bar.
Select a calculation type from the Calculation drop-down. This example uses % of previous row to compare the count of inventory items with the previous month's count of inventory items.
- Pivot calculation types will not appear unless Explore
results are pivoted.
Select the field on which you want to perform the calculation in the Source column drop-down. Only numeric fields that appear in the Explore's data table will be available to choose from. In this example, the user chooses to perform a % of previous row calculation on the Inventory Items Count measure.
Optionally, use the Format drop-down to choose a predefined format or create a custom format for the results. If you create a custom format, use Excel-style formatting as described on the Adding custom formatting to numeric fields documentation page. If no selection is made, Looker uses a default format.
Rename your table calculation from the default name in the Name field, if desired. The calculation name appears in the field picker and in the data table.
Select + Add Description to add an optional description of up to 255 characters that can give other users more context or information about the table calculation.
If you are finished creating the table calculation, select Save to add the calculation to the Explore.
The new calculation will automatically appear in the data table:
The new calculation will also appear in the Custom Fields section of the field picker if your instance is enabled for custom fields. As with other fields, you can select the calculation's name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation, including editing the calculation.
Duplicating table calculations
If you have the permissions to create table calculations, you can also duplicate existing table calculations that you or other users have created. Duplicating and then editing table calculations can be helpful if you'd like to create multiple table calculations with only small differences (for example, 30-day, 60-day, or 90-day sums).
There are a few ways to duplicate table calculations:
- Using a calculation's three-dot Options menu in the Custom Fields section of the field pickerUsing a calculation's data table gear menu (the only
way to duplicate table calculations if your instance is not enabled for custom fields)
If the Custom Fields Labs feature is enabled for your instance and you're allowed to create and edit table calculations, you can use the field picker three-dot Options menu to duplicate a table calculation. Additionally, you can use the method using a field's gear menu in the Explore data table.
To duplicate a table calculation with the field picker:
In the field picker, expand the Custom Fields section.Select the three-dot Options menu for the table calculation that you want to duplicate.Select
Duplicate. The duplicated table calculation appears below the original, using the name of the original table calculation plus the word "Copy" appended to the end:
Next, you can edit the duplicated table calculation.
You can also duplicate a calculation by selecting Duplicate from the calculation's data table gear menu:
If your instance is not enabled for custom fields, this is the only way to duplicate calculations.
For instances with custom fields enabled, the duplicated table calculation appears below the original, using the name of the original table calculation plus the word "Copy" appended to the end:
Next, you can edit the duplicated table calculation.
Editing table calculations
If you have the permissions to create table calculations, you also can edit existing table calculations that you or other users have created.
There are a few ways to edit table calculations:
- Using a calculation's three-dot
Options menu in the Custom Fields section of the field pickerUsing a calculation's data table gear menu (the only way to edit table calculations if your instance is not enabled for custom fields)
If your Looker instance is enabled for custom fields and you are able to create table calculations, you can use the field picker to edit table calculations. Additionally, you can use the method using a field's gear menu in the Explore data table.
To edit a table calculation with the field picker:
Expand the Custom Fields section.Select the three-dot Options menu for the table calculation that you want to edit.Select Edit to open the Edit table calculation pop-up.
Select a new calculation type from the Calculation drop-down, if desired.
Change the calculation definition, including a shortcut calculation's Source column, as desired. Only numeric fields that appear in the Explore's data table are eligible for calculation types other than Custom expression.
- If Custom expression is selected from the Calculation drop-down, either add a Looker expression to, or edit an existing Looker
expression in, the large text box. You can only create table calculations from fields that appear in the Explore's data table.
Select a new format from the Format drop-down, if desired.
Enter a new calculation name in the Name field, as desired. The calculation name appears in the field picker and in the data table. If you have changed anything on a table calculation, consider modifying the name to match.
Add or update an optional field description of up to 255 characters with details about the table calculation, including its intended use.
- If there is an existing description, the Description box will automatically appear. If there is no existing description, select + Add Description to add an optional description.
Select Save.
If your Looker instance is enabled for custom fields and you have the permissions to create table calculations, you can also edit a calculation by using the calculation's data table gear menu:
Select Edit calculation from the field's gear menu to open the Edit table calculation pop-up.
Select a new calculation type from the Calculation drop-down, if desired.
Change the calculation definition, including a shortcut calculation's Source column, as desired. Only numeric fields that appear in the Explore's data table are eligible for calculation types other than Custom expression.
- If Custom expression is selected from the Calculation drop-down, either add a Looker expression to, or edit an existing Looker
expression in, the large text box. You can only create table calculations from fields that appear in the Explore's data table.
Select a new format from the Format drop-down, if desired.
Enter a new calculation name in the Name field, as desired. The calculation name appears in the field picker and in the data table. If you have changed anything on a table calculation, consider modifying the name to match.
Add or update an optional field description of up to 255 characters with details about the table calculation, including its intended use.
- If there is an existing description, the Description box will automatically appear. If there is no existing description, select + Add Description to add an optional description.
Select Save.
Deleting table calculations
If you have the permissions to create table calculations, you also can delete table calculations that you or other users have created. When you delete a table calculation, it disappears from the Explore but not from any Looks or dashboard tiles that use that calculation. Also, anyone who is using a URL for an Explore that had the custom field will still see the calculation.
There are a few ways to delete table calculations:
- Using a calculation's three-dot Options menu in the Custom Fields section of the field pickerUsing a calculation's data table gear menu (the only way to delete table calculations if your instance is not enabled for custom fields)
If your Looker instance is enabled for custom fields and you are able to create table calculations, you can use the field picker to delete table calculations. Additionally, you can use the method using a field's gear menu in the Explore data table.
To delete a table calculation with the field picker:
Expand the Custom Fields section.Select the three-dot Options menu for the table calculation that you want to delete.Select Delete.You can also use the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows) to delete custom fields.
You can reinstate a custom field that you've deleted by selecting the back arrow on your browser.
You can also delete a calculation by selecting Delete from the calculation's data table gear menu:
This is the only way to delete a table calculation if your instance is not enabled for custom fields.
You can also use the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows) to delete custom fields.
You can reinstate a custom field that you've deleted by selecting the back arrow on your browser.
Sorting table calculations
To sort on a table calculation, select the field name the top of the column, just as you would a dimension or measure.
When table calculations cannot be sorted
Sorting on a table calculation works similarly to sorting on a dimension or measure in Looker. However, there are two important differences that prevent sorting in some scenarios:
- Table calculations are created after the data is retrieved from your database, which means that when you sort a table calculation, you can only sort the data that is already displayed.Some table calculations are applied to multiple rows within the same column (for example, when using an offset()
function (see more on using the offset and pivot_offset functions in Looker's Help Center). In these cases, sorting the table calculation would change its results and is therefore disabled.
Specific scenarios where you can't sort a table calculation include the following:
- When you're using a
calculation that hits a row limitWhen you're using a dimension or measure after you've already sorted by a table calculationWhen you're using a
table calculation that makes use of an offset
Calculations that hit a row limit
If the number of rows in your query exceeds the row limit that you've set, you will not be able to sort table calculations. This is because table calculations are only based on the rows that are displayed. Therefore, if you hit a row limit, the table calculation might be missing some rows that it should be sorting into your results. If you run into this issue, you can try increasing your row limit (up to 5,000 rows).
For example, the following table displays the 10 top-selling categories in an e-commerce store, sorted by total sales. Notice that the 10-row limit has been reached, which you're warned about by the yellow bar displayed the top of the table:
However, if you want to show the top 10 categories by number of orders instead, the results would look like this:
Sorting a dimension or measure after sorting a table calculation
As indicated in the Calculations that hit a row limit section on this page, table calculations are only based on the rows that are displayed. In contrast, sorting by a dimension or a measure goes back to your database to make sure it finds the correct rows. As a result, you should start sorting with dimensions and measures. Then, when the correct data has been returned from your database, you can sort those results based on a table calculation.
Calculations that use an offset function cannot be sorted
Any table calculation that makes use of an offset cannot be sorted, because the sort order of the rows would change the results of the offset.
For example, the following table calculation displays the percentage change in weekly sales for the Accessories category:
This only makes sense if the results are sorted by the week.
Using table calculations in visualizations
Just like LookML-defined dimensions and measures, table calculations are automatically displayed in visualizations.
In addition, you can use table calculations to decide which rows of your data should be displayed in a visualization. The following example will be used to explore this feature; this example includes weekly sales information about the Accessories category. Note that the underlying data table includes the dimension Orders Created Week and the measure Total Sale Price, along with a table calculation called Percent of Previous Week Sales, which compares the revenue of each week against the previous week:
You can now prevent certain rows of data from showing up in the column chart. To do so, you'll create a table calculation that evaluates to true or false, then hide the false values (which will appear as "No" entries in your data table). You don't want the formula to result in the word "true" or "false"; rather, it should be a condition that is either true or false.
To achieve this, you could create a table calculation, Exceeds Previous Week Sales, that evaluates whether the Percent of Previous Week Sales calculation is greater than 1:
$percent_of_previous_week_sales > 1This will result in a table that includes a new table calculation that evaluates each row against the Exceeds Previous Sales calculation and that displays a Yes or a No, depending upon whether the percent of previous is greater than 1:
To hide all the rows where a particular week's revenue did not exceed the revenue of the previous week, select the logical calculation's gear icon and select Hide "No"s from Visualization.
The resulting visualization will now display only the weeks that exceeded the previous week's revenue:
Looker does not re-run queries to exclude fields or values that are hidden using Hide from Visualization or Hide "No"s from Visualization for table calculations. As a result, calculations based on fields with hidden Explore values may display unexpected results.
One common use case for this feature is hiding the first or last row from a visualization, since many types of analyses create bad rows the beginning or end of a table. For example, you might decide to hide the first or last row when you are calculating running totals, when you have a partial day that ends a date analysis, or, like the following example, when you are calculating a percent of the previous row:
To get rid of that row, simply create a new table calculation to filter out the null value by using the is_null logical function:
NOT is_null($percent_of_previous_week_sales)Then, hide the row by selecting Hide "No"s from Visualization from the table calculation's data table gear icon menu.
Considerations for using table calculations
- All the fields that you use in your
table calculations MUST be part of your initial query.Formulas must be in lowercase. ROUND will not work, but round will.Table calculations will only operate over rows that are returned in your query. If there is a 500-row limit, the 501st row will not be considered.If you add a Total row to your data table, some table calculations that perform
aggregations, such as calculations that use percentile or median, might not add up as you expect. This is because table calculations calculate totals using the values in the Total row, not using the values in the data column. See the Display potentially confusing table calculation totals as nulls Help Center article for troubleshooting
tips.Always use leading zeroes for decimals less than 1. For example, 0.95 will work, but .95 will cause an error.Using the Command-K (Mac) or Ctrl+K (Windows) keyboard shortcut will clear all table calculations, including custom fields. To reinstate your table calculations, select the back arrow on your browser. You may also need to re-run your query.
When coding a definition for a column that will contain a high percentage of null values What attribute can you use to optimize the storage?
Sparse Column is a very efficient feature that can be used to store NULL values in a database table with a high NULL values percentage.What button do you click to see the results of a query?
To see the query results, on the Design tab, click Run. Access displays the results of your query in Datasheet view.What is a type of field that displays the result of an expression?
Calculated Field. A field that displays the result of an expression rather than data stored in a field.Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?
The OFFSET clause specifies the number of rows ot the result table to skip before any rows are retrieved. Tải thêm tài liệu liên quan đến nội dung bài viết Which functions perform a calculation on the values of a column from selected rows group of answer choices?