Creating Custom Columns

The data doesn’t contain all the columns that we’d like for analysis, such as Total Including Tax, and Account Age. We’ll create them now.

  1. Under Queries, select OrderDetails.
    Go to Add Column Tab > Click Custom Column Custom Column

  2. Under New column name, type in TotalIncludingTax. Enter the formula as shown
    [Quantity]*[UnitPrice] + [Quantity]*[UnitPrice]*[TaxRate]/100 Enter Formula

  3. Go to Transform tab > Change Data Type to decimal number. Change Type to Decimal Numebr

We’re going to create an Account Age column to calculate how many years each account has been in existence.

  1. Select the AccountOpenedDate column from Customers Query Create an Account Age Column

  2. Go to Add Column tab > Click Date > Select Age
    A new column called Age is created at the end of the table. Select Age

  3. Now we want to change it from duration to number of years
    Go to Transform tab > Click Duration > Select Total Years Change Duration to Years

  4. Still on the Transform tab > Change the type from Decimal Number to Whole Number. Change Type

  5. Double-click the Age column heading then rename it to AccountAge Heading Renaming

Now we’re going to create a sort column that will be used later in the day

  1. Select the Date Query

  2. In the Add Column menu select add a Custom Column

  3. Under New column name, type in MonthYearSort. Enter the formula as shown
    ([Calendar Year]*100)+[Calendar Month Number] Calendar Month Sort Field