Data Strategy | Tips & Tricks

When preparing data to be used within a dashboard project, some data analysis is usually required. Moreover, sometimes you’ll need to view figures in your dashboard that simply aren’t present in your existing data set. To solve this, you can extract data from existing columns and perform some transformations upon it, or simply “manufacture” the data you need.  To do this in iDashboards, we use what we call “expressions”.  Expressions use either Javascript or SQL to tell the data either how it should change, or to generate new data points that can be incorporated into a dashboard.

Expressions will require at least 1 variable, but can have as many as necessary to complete the task at hand.  There are two types of expressions: Those derived from the data, or those that can be “manufactured.”

Data Derived Expressions

There are many different kinds of expressions that derive from data. Extracting numbers from existing data can involve date transformations, like adding or removing days from dates in the data, or simply aggregating raw numbers to create a meaningful whole. You can also perform math on your data using expressions, like arithmetic or returning absolute values.

For example, you may have sales from multiple divisions, resulting from different products, regions, etc. What you really want to be looking at is a total sales number, however. Expressions make it easy to aggregate all of the relevant data points to give you the view you want. By taking all of the different groupings, and adding all of the sales together, or summing the data, we can get a total sales for the entire company. The smaller segments of data are still available within the data, of course. If you want to give the end user the option to view these, we recommend letting them drill down into the data for a deep dive.

Data Derived Expressions

Let’s say we wanted to aggregate data based on a sales month. In this case, however, all of our transactional data has a specific date and time but the month string isn’t its own data column. We can use a SQL query of datediff(month, dateadd(month, 0, SalesDate), 0) to pull the SalesMonth from the SalesDate.  Now we can see monthly trending, where before we would have been inundated with too many dates on a trend line.

Date Diff Expression

In another case, we want to view average performance of our support team. We can establish an average rating for all of our technicians, using a mean function on Javascript, or Avg function in SQL, and then laying that average on top of how many tickets each is doing in a day. These sorts of insights are critical when it comes to managing performance. Letting each tech know if they are above or below the average, can help drive improvement. Without using expressions, we wouldn’t be able to visualize or communicate that average.

SQL Expression Example: Avg Function

SQL Expressions

Javascript Expression Example: Mean Function

Javascript Mean Function

“Manufactured” Expressions

Manufactured means that we are creating the data point or value that we wish to see (like a date) from scratch within the expression, because it does not exist in our data set. For example, we may want to have today’s date on our dashboard, but it isn’t anywhere in our data.  Fortunately we can use an expression to get that for us.  In SQL, we can use getDate(). In Javascript, you can use var d = new Date().  Both of these are examples of how we can manufacture a piece of data out of “nowhere.

SQL Expression Example: getDate()

SQL getDate()

Javascript Expression Example: var D = new Date()

Javascript var D = new Date()

There is a way to get just about any numbers you are looking for, from the existing data that you have.  It may take some doing, but rest assured, using various expressions to transform your data, you can have the numbers you are looking for, to help drive your business.

Alternative Text

Jim Qussar

Jim Qussar is our Sr. Pre-Sales Engineer, with over 20 years IT experience. He’s known as the office blacksmith. Every office has one right?


Your email address will not be published. Required fields are marked *