Contents x
      Step 4b - Prepare Data for Reporting - Create Reporting Dimensions & Metrics
      • 10 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Contents

      Step 4b - Prepare Data for Reporting - Create Reporting Dimensions & Metrics

      • 10 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Article summary

      NOTE

      This article is part of the "Tutorial - Basic Sales Dashboard" series. It assumes the following:

      • The user is logged in to an active DataJet realm that contains the DJDemo2023 data (or data arranged in a similar structure)

      • Demo Data (or data in a similar structure) has been loaded into a project and reporting tables have been created.   See Step 3 - Create Reporting Tables if this has not yet been done.

      • This step can be skipped if reporting dimensions and metrics have already been created.

      Approach

      The Dashboard we are creating makes use of Time Periods (Today, Month To Date, Year to Date) and Customer Type (New or Repeat).  In order to build the dashboard, new fields need to be created so they can be used as dimensions and filters.  This section shows how to Create Reporting Dimensions & Metrics, including fields for:

      • Today

      • Month To Date

      • Year To Date

      • Identifying New and Repeat Customers

      The dashboard is designed to be dynamic so that it can be automatically recreated as part of a scripted process after a data refresh.  The examples below show how to create dynamic filters that will always reflect the latest set of transactions in the current sales table.

      Key Techniques

      The following functional areas are introduced by this step:

      Video Tutorial

      Quick Links

      Time Periods

      The various metrics on the dashboard are split out by:

      • TODAY - things that happened today

      • Month To Date - activity since the start of the current month

      • Year To Date - activity since the start of the current year

      Definitions

      The following definitions are used:

      • TODAY -  The filter TODAY (which is a date value) is the date of the most recent transaction date in the transaction table.  It is precisely defined as;

        • Maximum value of [sales.ship date]

      • Month To Date- This uses an engineered field called MONTHORDINAL.  This gives a value of 1 to all transactions that occurred in the current month, 2, to all transactions that occurred in the previous month, and so on.

        • ship date MONTHORDINAL = 1 is current month

        • ship date MONTHORDINAL = 13 is same month last year

      • Year To Date- This uses YEARORDINAL in conjunction with TODAY and DOY (Day Of year).   

        • ship date YEARORDINAL gives the number 1 to all transactions that occurred in the current year, 2 to the previous year, 3 to the previous year etc

        • ship date DOY calculates the day number of the transaction (where 1 is January 1st and 365 is Dec 31)

        • TODAY DOY is the day number for TODAY


      Calculating TODAY filters

      To visualise dynamic daily information, the following approach is taken:

      1. Work out what date TODAY is: From [sales.ship date], extract the value of the most recent transaction and label this as [sales.TODAY]

      2. Flag records that were shipped "today":  Use FLogic to create the flag field [sales.IsToday] where [sales.ship date] = [sales.TODAY]

      3. Calculate which day of the year a transaction was shipped: Calculate the day of the year from [sales.TODAY] so that "Month to Date" and "Year To Date" filters can be created - [sales.TODAY DOY]  

      4. Work how long it is since a transaction was shipped: Calculate [sales.days since shipping] by using DaysBeforeMax(A) to work out how many days before TODAY the transaction was shipped

      5. Flag records that were shipped a week ago: Calculate the flag field [sales.IsWeekAgo] where [sales.days since shipping]=7

      Jump To:

      Calculating [sales.TODAY]

      To calculate  [sales.TODAY]:

      • Click on Engineering | Expression Field  and the Expression Field Dialog will appear. 

      • Expand the available Functions options, 

      • Click on Field Attributes, fmax and select Use Function. 

      • Drag and Dropship date onto the fmax([field]). 

      • Rename the function to TODAY and select Build.



      The TODAY field will appear under the sales table in project explorer. This field stores the value of the current date.

      Value of TODAY in the Demo Database

      The data for this tutorial has a most recent transaction date of 2015-01-07, therefore "Today" is 7th January 2015 in these examples

      {  "method": "BuildBakedField",  "name": "TODAY",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "dataType": "default",  "function": "dmath",  "p1": "expression",  "p2": "fmax([ship date])",  "nullIsZero": false,  "NOORD": false }

      Calculating [sales.IsToday]

      To calculate [sales.IsToday]:

      • Click on Engineering | Function Field | Functions | FLogic | A = B  and the Function Field Dialog will appear. 

      • Drag and Drop ship date onto Option A 

      • Drag and Drop TODAY onto Option B. 

      • Rename the function to IsToday and select Build.

      The field IsToday will appear in the sales table in project explorer. Click the field and see in the Context panel that there are 504 records from today:


      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "IsToday",  "function": "flogic",  "p1": "A=B",  "p2": "ship date",  "p3": "TODAY" }


      Calculating [sales.TODAY DOY]

      To calculate [sales.TODAY DOY]:

      • Right-click on [sales.TODAY] and go to Engineering | Function | DateTransform | DOY(A). The function dialog will appear.

      • Rename the function to TODAY DOY and select Build.

      The value "7" will be stored for each field, as TODAY's date (07/01/2015) has a Day of the Year of 7.

      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "TODAY DOY",  "function": "datetransform",  "p1": "DOY(A)",  "p2": "TODAY" }

      Calculating [sales.Days Since Shipping]

      To calculate [sales.Days Since Shipping]:

      • Click on Engineering | Function Field | Functions | DateTransform | DAYSBEFOREMAX(A)  and the Function Field Dialog will appear. 

      • Drag and Dropship date onto Option A. 

      • Rename the function to days since shipping and select Build.

      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "days since shipping",  "function": "datetransform",  "p1": "DAYSBEFOREMAX(A)",  "p2": "ship date" }

      Calculating [sales.IsWeekAgo]

      To calculate [sales.IsWeekAgo]:

      • Drag and Drop[sales.days since shipping] onto the HotBar. The Add DataSet Row Dialog will appear.

      • Set the Operator value to = 7 and press OK. 

      • Right click on the DataSet icon that has just been created and select Save. The Save DataSet Dialog will appear.

      • Change the name to IsWeekAgo

      • Make sure Save as Field is selected. 

      • Press OK.

      The field IsWeekAgo will appear in the sales table in project explorer. Click on the new field to see that there are 6,888 records from a week ago:

      {  "method": "SaveDataSetAsField",  "project": "SDJ-DJDemo2023",  "name": "IsWeekAgo",  "overwrite": true,  "dataSet": {    "logic": "or",    "name": "IsWeekAgo",    "strict": true,    "set": [      {        "logic": "and",        "stype": "FIELD",        "entity": {          "type": "field",          "name": "sales.days since shipping",          "table": "sales",          "valueFilter": []        },        "op": "=",        "values": [          "7"        ]      }    ]  } }


      Calculating MONTH TO DATE filters

      To visualise dynamic month to date information, the following approach is taken:

      1. Extract YEARMONTH so that all data is grouped by month: From ship date, extract the YEARMONTH portion of the date ([sales.ship date YEARMONTH])

      2. Assign a number to each month, with 1 = current month: Extract the underlying numeric index from [sales.ship date YEARMONTH] using DescendingIndex to get the MONTHORDINAL ([sales.ship date MONTHORDINAL])

      3. Calculate which day of the year each transaction was shipped: Calculate the day of the year (where Jan 1st = 1 and Dec 31st = 365/366) to get [sales.ship date DOY]

      4. Flag records that were shipped on or before the current Day Of The Year: Use FLogic to create flag field [sales.IsYearToDate] of all transactions that occurred on or before the current day of the year where:

        1. [sales.ship date DOY] <= [sales.TODAY DOY]

      Jump To:


      Calculating [sales.ship date YEARMONTH]

      To calculate [sales.ship date YEARMONTH]:

      • Right-click on ship date and select go to Engineering | Function | DateTransform | YEARMONTH(A). The Function Field Dialog will appear.

      • Rename the function to ship date YEARMONTH and select Build.

      The field ship date YEARMONTH branch will appear in the sales table in project explorer.

      This shows the year followed by the month of the year in the format YYYYMM.

      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "ship date YEARMONTH",  "function": "datetransform",  "p1": "YEARMONTH(A)",  "p2": "ship date" }

      Calculating [sales.ship date MONTHORDINAL]

      To calculate [sales.ship date MONTHORDINAL]:

      • Right-click on ship date YEARMONTH and select Engineering | Function | General | DescendingIndex. The Function Field Dialog will appear.

      • Rename the function to ship date MONTHORDINAL and select Build.

      The field ship date MONTHORDINAL branch will appear in the sales table in project explorer. This ship date MONTHORDINAL indicates for each sales record how many months ago the transaction took place relative to the current month:


      It is good practice to check that field has been created as expected. To do this:

      1. Open a profile on ship date MONTH

      2. Drag and Drop ship date MONTHORDINAL into the Dimensions box.

      3. Click on the header ship date MONTHORDINAL and the data will rearrange itself into ascending order of ship date MONTHORDINAL.

      4. Click the header again and the data will be rearranged into descending order of ship date MONTHORDINAL.

      5. Verify that MONTHORDINAL and YEARMONTH align as expected.


      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "ship date MONTHORDINAL",  "function": "descendingindex",  "p1": "ship date YEARMONTH" }

      Calculating [sales.ship date DOY]

      To calculate [sales.TODAY DOY]:

      • Right-click on [sales.ship date] and go to Engineering | Function | DateTransform | DOY(A). The function dialog will appear.

      • Rename the function to ship date DOY and select Build.


      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "ship date DOY",  "function": "datetransform",  "p1": "DOY(A)",  "p2": "ship date" }


      Calculating [sales.IsYearToDate]

      Flags records that were shipped on or before the current Day Of The Year: Use FLogic to create flag field [sales.IsYearToDate] of all transactions that occurred on or before the current day of the year where: [sales.ship date DOY] <= [sales.TODAY DOY]

      To calculate [sales.IsYearToDate]:

      • Right-click on [sales.ship date DOY] and go to Engineering | Function | FLogic| A<=B. The function dialog will appear.

      • Drag and Drop [sales.TODAY DOY] onto parameter B

      Rename the function to IsYearToDate and select Build.


      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "IsYearToDate",  "function": "flogic",  "p1": "A<=B",  "p2": "ship date DOY",  "p3": "TODAY DOY" }


      Calculating [sales.IsMonthToDate]

      There is no need to explicitly calculate the field [sales.IsMonthToDate].  It can be created using existing fields:

      • Drag and Drop [sales.ship date MONTHORDINAL]=1 onto the HotBar. This will select all records for the current month (23,688) - note this is the same as all records in the year to date as in the demo dataset, the current month is January

      • AND [sales.IsYearToDate]=1

      IsMonthToDate for previous years

      To create IsMonthToDate for years other than the current year, create a dataset with the following rule:

      • [sales.ship date MONTHORDINAL]=1 + (N*12) (where N is the the year in question: 0 = current year, 1 = previous year etc. The ordinal for the same month in the previous year would be 13.

      • AND [sales.IsYearToDate]=1


      Calculating YEAR TO DATE filters

      To visualise dynamic year to date information, the following approach is taken:

      1. From ship date, extract the YEAR portion of the date ([sales.ship date YEAR])

      2. Extract the underlying numeric index from [sales.ship date YEAR] to get the YEARORDINAL ([sales.ship date YEARORDINAL])

      3. Calculate the day of the year (where Jan 1st = 1 and Dec 31st = 365/366) to get [sales.ship date DOY]

      4. Use FLogic to create flag field [sales.IsYearToDate] of all transactions that occurred on or before the current day of the year where:

        1. [sales.ship date DOY] <= [sales.TODAY DOY]

      Jump To:

      Calculating [ship date YEAR]

      [sales.ship date YEAR] is created using the DateTransform function YEAR:

      • Right-click on [sales.ship date] and go to Engineering | Function | DateTransform | YEAR(A)

      • The Function Field Dialog will appear. 

      • Rename the function to ship date YEAR and select Build.

      [ship date YEAR] will appear in the sales table in Project Explorer:

      The JSON method used to create the field is as follows:

      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "ship date YEAR",  "function": "datetransform",  "p1": "YEAR(A)",  "p2": "ship date" }


      Calculating [ship date YEARORDINAL]

      To create [sales.ship date YEARORDINAL]:

      • Right-click on ship date YEAR and select to Engineering | Function | General | DescendingIndex 

      • The Function Field Dialog will appear. 

      • Rename the function to ship date YEARORDINAL and select Build.

      The [ship date YEARORDINAL] field will appear in the sales table in project explorer. 

      [ship date YEARORDINAL] is a record of how many years it is since the current year that each transaction occurred

      Current year

      Note - in this tutorial, the data that is being used has a most recent transaction date of 7th Jan 2015, and so 2015 is taken as the current year


      It is good practise to check that the engineered data looks as expected. To do this:

      • Select [sales.ship date YEAR], right-click and choose Profile

      • Drag and Drop [sales.ship date YEARORDINAL] into the Dimensions box on the open profile report

      Click on the header ship date YEARORDINAL and the data will rearrange itself into ascending order of ship date YEARORDINAL.

      This confirms whether ship date YEARORDINAL matches with the expected value of [ship date YEAR]

      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "ship date YEARORDINAL",  "function": "descendingindex",  "p1": "ship date YEAR" }

      Calculating [sales.ship date DOY]

      To create [sales.ship date DOY]:

      • Right-click on [sales.ship date] and go to Engineering | Function | DateTransform | DOY(A). The function dialog will appear.

      • Rename the function to [ship date DOY] and select Build.

      {  "method": "BuildBakedField",  "project": "SDJ-DJDemo2023",  "targetTable": "sales",  "overwrite": true,  "name": "ship date DOY",  "function": "datetransform",  "p1": "DOY(A)",  "p2": "ship date" }



      Was this article helpful?
      What's Next