Dynamic Column (Calculated Column)

Sintel BI has a  powerful feature called ‘Dynamic Column,’ allowing users to create custom fields within a data source with values dynamically computed based on JavaScript expressions. This feature enhances the flexibility and customisation options for users working with data in Sintel BI.

How to Add a Dynamic Column:

1. Navigate to the desired data source tile in Sintel BI.
2. Click on the f(x) icon located at the top of the data source tile.
   
3. Give the calculated column a name to easily identify it.
4. Add the formula using JavaScript expressions. You can utilize the ‘@’ symbol to easily include field values in your formula.
5. Select the column type based on your data requirements: Single Line of Text, Number, Date/Time, or Yes/No.
6. Save your changes.

Examples of Use:

1. Track Project Status and Days Overdue:

• Column Name: “Days Overdue”
• Formula: Interval.fromDateTimes(DateTime.now(), DateTime.fromISO({[Deadline]})).length(‘days’)
• column Type: Days Overdue =NumberUsage: In this example, the user has added two calculated columns to track project status and days overdue:

1. Status Column: This column indicates whether a project is “Due” or “Overdue” based on the project’s deadline compared to the current date.
2. Days Overdue Column: This column calculates the number of days overdue for each project. It utilizes the Interval.fromDateTimes() function in JavaScript to compute the length of time between the current date (DateTime.now()) and the deadline date ({[Deadline]}), which is a field value in the data source.

By adding these calculated columns, users can gain insights into project statuses and identify overdue projects at a glance, thus facilitating better decision-making and project management within Sintel BI

2. Calculating Profit Margin:

• Column Name: “Profit Margin”
• Formula: ({[Revenue]} – {[Cost]}) / {[Revenue]} * 100
  
• column Type: NumberUsage: This formula calculates the profit margin percentage using revenue and cost data from your source. To use it effectively, ensure you have columns representing revenue (number) and cost (number) within your dataset. When incorporating the formula into your dynamic column, remember to tag your revenue field as ‘@revenue’ and your cost field as ‘@cost’. The formula should then appear as follows: (@revenue – @cost) / @revenue * 100. Tagging ensures that the formula accurately accesses and calculates the profit margin based on your specific revenue and cost values.

3. Calculating Duration in Hours Between Start and End Times:

• Column Name: “Duration (Hours)”
• Formula:

  (function() {
      try {
          var differenceInHours = DateTime.fromISO({[Course_x003a_EndTimeString]}).diff(DateTime.fromISO({[Course_x003a_StartTimeString]}), "hours").hours;
          if (isNaN(differenceInHours)) return null;
          return Math.round(differenceInHours);
      } catch {
          return null;
      }
  })()

   

• column Type: NumberUsage:

  This example demonstrates how to calculate the duration in hours between two time fields: a course start time and an end time. The formula uses the DateTime.fromISO() method to convert the start and end time strings into date-time objects and then calculates the difference in hours using the .diff() method. The result is rounded to the nearest hour using Math.round(). If the calculation encounters invalid inputs or errors, it returns null. This column is particularly useful for scenarios such as tracking course durations or shift lengths.

Additional Tips:

• Use JavaScript expressions to create dynamic and conditional calculations.
• Leverage existing field values in your formulas using the ‘@’ symbol.
• Test your formula to ensure it produces the desired results before saving.

Conclusion:

The ‘Dynamic Column’ feature in Sintel BI empowers users to add dynamic, custom fields to their data sources, enabling enhanced analysis and reporting capabilities. Users can create versatile calculated columns tailored to their specific business needs by incorporating JavaScript expressions. Experiment with different scenarios to unlock the full potential of this feature and elevate your data analysis in Sintel BI.