JavaScript: Derived Columns

This will require Builder privileges for setup and implementation.

Derived columns use JavaScript for their expressions. The following are Fundamentals of JavaScript:

• Declaring Variables
• Conditional Statements
  • If... Statement
  • If...Else Statement
  • Switch Statement
• Operators
  • Arithmetic
  • Assignment
  • Comparison
  • Logical Comparison
• Derived Columns

Question:

Some charts need to have additional columns built off of existing column data. Can derived columns be used in iDashboards charts?

Answer:

Derived Columns are available to be configured in charts and display data that is derived from other data in the data set and will be the result of a mathematical expression written in JavaScript. When creating a chart, it is common to assign each of your Y-axes to a column from the data source. Configured in this way, each Y-axis will simply display the data stored in each column. However, some situations may require one or more of your Y-axes to display data that is a result of a calculation performed on data from the data source. To accomplish this you would configure each of these Y-axes as a Derived Column. Consider the following:

• the X-axis maps to the Product column
• the Y1-axis maps to the Sales_Previous_Year column
• the Y2-axis maps to the Sales_Current_Year column.

In addition to displaying the previous year and current year sales figures in your chart, suppose you would like to display the percent increase or decrease in sales from year to year, for each product. This can be accomplished by adding a derived column to the chart in the Y3 position, called Percent Change, which values are derived from a calculation performed on Y1 and Y2. The calculation would be the following JavaScript expression:

(y2 - y1) / y1 * 100;

When the chart is loaded, the server will evaluate the expression for every row in the data set and insert the result in the Y3 column. The resulting data set would show three columns of data. Y1 and Y2 would be the same as previous, but Y3 would now be present with the solution to the expression that was entered as the data for the column. Note the Y3 or Percent Change column has (Expression) selected for its Data Column. When you select (Expression) for a Data Column, the Define an Expression for Axis Expression window appears. This is where you enter the JavaScript expression that will be used for the Derived Column.

Limitations:

1. No filtering on derived columns
   • It is not possible to apply a filter to a derived column due to the fact that derived column expressions are evaluated after all filtering has taken place.
   • If you are configuring a filter on a table that uses a derived column, the derived column will simply not show up as an option when selecting the filter’s data column.
2. No drilldown filtering on derived columns
   
   • It is common to configure one chart to drill down into another chart and to have the second chart perform a drilldown filter.
   • A drilldown filter automatically selects a value in the second chart’s pivot that corresponds to the value the user clicked in the first chart.
   • Since drilldown filtering is a form of filtering, it cannot be used in conjunction with a derived column

For More Information:

• iDashboards Builder Manual 14 Analytics
• JavaScript Expression Help

Disclaimer: iDashboards Technical Support Engineers are not Data Analysts who know in depth SQL Queries or JavaScript Expressions. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success. If you have issues with in depth queries or expressions please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this issue, and if necessary we will refer you to your Client Success Manager to schedule time with a Principal Product Engineer.

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.