Sometimes there is a need to tune a data for particular chart. In those cases you can use data transformation feature to cover following needs:

1. Convert dates to browses locale.

2. Create additional dimensions with complex calculations.

3. Add new rows to data or remove unneeded.

Data transformation is available only for advanced chart gadget.

Using data transformation

In data transformation you write custom javascript function (or lambda). This functions accepts only one parameter which is an array of rows. Each row has properties with names corresponding to a selected data columns names.

// example of a function
function addAdditionalDimension(data) {
  return data.map(row => {...row, newDimension: row.columnA + row.columnB})
}

// example of a lambda
data => data.map(row => {...row, newDimension: row.columnA + row.columnB})

// another example of injecting additional row
function addMissingRow(data) {
  const missingRow = {...};
  data.push(missingRow);
  return data;
}

Example usage of data transformation

Consider a query of min, max and average comments length per day which we want to visualize as a plot in Jira. For this need we want to create a new view with following steps:

1. Go to Smart QL → New query and place following query

   SELECT 
   	floor(created to day) creationday, 
   	max(CHAR_LENGTH(body)) maxcommentlength, 
   	avg(CHAR_LENGTH(body)) avgcommentlength, 
   	min(CHAR_LENGTH(body)) mincommentlength 
   FROM 
   	CORE.comments 
   GROUP BY 
   	floor(created to day)

2. Save a query as EXAMPLE_VIEW

Now you will have an example view as shown below:

Lets create new plot.

1. Add new gadget to the dashbpard. Choose Smart QL Advanced Gadget. Select newly created EXAMPLE_VIEW as a data feed.

2. In configuration place following JSON snippet.

   {
     "type": "line",
     "x": "X",
     "y": "Y",
     "color":"C",
     "plugins": [
       {
         "name": "tooltip"
       },
       {
         "name": "legend"
       },
       {
         "name": "export-to"
       }
     ]
   }

3. In data transformation place following javascript lambda transformation.

   data => data.reduce((acc, curr)=>[
     ...acc,
     {X: curr.creationday, Y: curr.maxcommentlength, C: 'max'},
     {X: curr.creationday, Y: curr.mincommentlength, C: 'min'},
     {X: curr.creationday, Y: curr.avgcommentlength, C: 'avg'}
   	], [])

Now you will see following plot consisting of 3 data series.