Use Formulas with Ease with ClicData

Let’s be honest. BI dashboards are really only as good as the metrics and insights they provide to their users. The more complex calculations that can be put in play behind the scenes, the more meaningful the insights, and the greater their value to the business. Yet many BI analysts are disappointed to discover that many dashboard platforms don’t quite deliver on ease-of-use when it comes to formula development.

As a data-warehousing platform, ClicData lets you connect data from all corners of the business in order to provide the depth of data that give managers and executives the actionable insights they need to run the business.

But just as importantly, the ClicData dashboard platform provides a full range of formulas that make it fast and easy for BI analysts and IT support to deliver effective KPIs and other valuable metrics. That means you can deliver the in-depth reporting and meaningful insights that help the business stay competitive.

You can work with formulas in the back end at the level of the data, and at the front end at the level of the dashboards. Each way has different advantages which you’ll want to leverage.

Add calculated columns to your data

Do you need to add calculated columns to your data? ClicData lets you perform data transformations using a simple click-and-drag visual interface. Our Create View feature is designed to let you apply transformations to your original data set that your dashboards can access in any way you see fit.

• Example 1
  If you’re working with dates, you might want to calculate the difference in months, days and hours between two dates and create an aggregated field from the result. ClicData provides an extensive library of date field formulas that use Transact-SQL. Take a look here.



• Example 2
  If you’re going to want to add new columns to your dashboards, you can still get the data ready on the data side. For example, you can add Fixed Cost and Variable Costs and create a new column for Total Cost. Or, you can subtract two columns, such as Purchase Price minus Selling Price. You can even calculate ratios you’ll need for profit margins, growth ratios, and more. Note that percentages will be displayed per line, so you need to show the results at the same level of aggregation in the dashboard. Don’t try to do averages of percentages.



• You can also create entirely new calculated columns or create mappings and groupings in your data set with our Data Transformation feature. It’s amazing how fast and easy it is.

Add contextual data to your dashboard displays

At the dashboard level, you can also use ClicData formulas directly in the charts, tables, and other widgets you use. Calculations work dynamically on the data, reporting in real time. And any filters you’ve applied will affect the result.

You can use ClicData’s Chart Function to add a new column to a dashboard chart or table that displays aggregated results that are already provided from the data set. Or to calculate the percentage of a column from the total.

• Example 3
  If you have a chart showing Sales and Budget figures, but you also want to see the ratio of each category value, you can do so pretty easily. Our auto-complete feature in our Formula Editor helps you quickly find the formula want. Just start typing the SeriesValues() formula. Here’s a quick tutorial.







• Example 4
  Let’s say your table shows Sales per Month and Year category groupings and you want to calculate month-over-month and year-over-year deltas, growth ratios and more. You’ll use the RowValue () function to refer to a cell from a previous row, as in RowValue( ‘column name’, -1). See it in action.

Find out more about using formulas in ClicData.

We’re here to inform and assist you! Don’t hesitate to contact us through chat, or send us an email to support@clicdata.com!