Changing Column Definition

Sometimes, the data in the base tables cannot be used directly, and must use an expression to "correct" it. Instead of applying this expression in every visual, it can be more effectively applied at attribute level, during dataset definition.

For example, when default percentages are reported as whole numbers instead of fractions, it is useful to perform the division by 100 by changing the attribute calculation.

The following steps demonstrate how to change the default calculation function from Sum to Average for the column life_expectancy. We imported the data from the file ca-graduation-rates.csv, and created a dataset CA College Readiness. See how to do this in Importing Data from Flat Files and in Creating Datasets from Imported Files.

  1. In the Datasets area, select CA College Readiness (main.ca_graduation_rates).

    Selecting a dataset
    Select Dataset 'CA College Readiness'
  2. In the Dataset Detail menu, select Attributes.

    Detail for Dataset 'CA College Readiness'
    Dataset Detail for 'CA College Readiness'
  3. In the Attributes area, select Edit Attributes.

    Editing Attributes for Dataset 'CA College Readiness'
    Editing Attributes of Dataset 'CA College Readiness'
  4. Under Measures, find the field percentage_of_grads..-, and click the (clone) icon next to it.

    Cloning a column
    Cloning a Column of a Dataset
  5. Under Measures, find the new cloned field Copy of percentage_..., and click it.

    The Column View window modal appears.

    Changing the expression of a column
    Changing the Column Definition/Calculation
  6. In the Column View modal, make the following changes:

    • Change Display Name to percentage_grads_with_college_required_courses.
    • Change Default Aggregation to Average.
    • Change Expression to [percentage_of_grads_with_uc_or_csu_required_courses]/100.

    Click Apply.

    Saving changes to column definition
    Saving Changed Column Definition
  7. [Optional] Click (eye) icon on the line with the original (base) field to hide it.
  8. Under Dataset: CA College Readiness, click Save.

    saving changed dataset attributes
    Saving Changed Dataset Attributes
  9. As a result of the change, the new column uses reports the percentage as a decimal, so that % display format can be applied correctly.

    To test this, click New Visual in the top left corner of the Dataset: CA College Readiness page.

  10. Populate the shelves from the available fields (Dimensions, Measures, and so on) listed in the left navigation menu.

    • Under Dimensions, select county, gender, and ethnicity, and place them on the Dimensions shelf.
    • Under Measures, select total_grads, percentage_of_grads_with_uc_or_csu_required_courses, and percentage_of_grads_with_college_required_courses, and place them on the Measures shelf.
  11. [Optional] Alias the fields on the Measures shelf as grads, bad%, and % ready for college.
  12. Click the icon to the right of the variable percentage_of_grads_with_uc_or_csu_required_courses, or bad%
  13. In the dropdown, click Display Format.
  14. In the Display Format modal window,

    • Under Category, select Percentage.
    • [Optional] Under Sample, enter a representative value, to preview the formatting changes.
    • Under Decimal Places, enter 2.

      To see all decimal places, select All.

    • Click Save.
    Applying Percentage Display Format
    Applying Percentage Display Format
  15. Click Refresh Visual.
  16. As you can see, the percentage reported is inaccurate. In fact, it is 100 times greater than it should be.

    Percentage Display Format Applied Incorrectly
    Percentage Display Format Applied Incorrectly
  17. Similarly, apply the same display format to the adjusted field, percentage_of_grads_with_college_required_courses, or % ready for college.
  18. As you can see, this formatted field reports the value accurately.

    Percentage Display Format Applied Correctly
    Percentage Display Format Applied Correctly