What is a calculated field?

  • Calculated fields in Lexio allow users to create new metrics from measures and dimensions that already exists in their data source. 
  • Lexio supports calculated fields made up of 1+ columns in the data by combining 1+ columns with basic mathematical operators (+,-,/,*)


Calculated Field: 

When would you need this?

  • When it is necessary for you to assess the performance of core metrics that don’t already exist in the data, including: changes over time, and understanding the 'why' behind these changes in order to make better, more data driven decisions
    • These can be metrics such as Cost Per Lead (CPL), Cost Per Click (CPC), Conversion Rates, Cycle Time, Utilization Rates, date calculations, etc.
    • In these scenarios, Lexio's new 'calculations' feature can be easily leveraged to track these types of metrics
  • Lexio also uses calculated fields to infer relationships between metrics in the What to Read Next recommendation algorithm. In the example above, Deals and Demo Requests would be related stories served up to users in Lexio.

What are prerequisites for creating a calculated field in Lexio?

  • Your data set transformed such that:
    • Each input into the calculation is a separate column in the data
      • For e.g., in the “Cost Per Lead” example above, you’ll want to have one column in the data for “cost” and another for “number of leads”
    • The 'grain' of the data are such that the calculation applies to the entire data set (rather than just a subset of the data)
      • For e.g., in the “Cost Per Lead” example above, the grain of the data are days or weeks, so Lexio can calculate and track CPL over time

How do I set up a new calculated field?

Step 1

    You can get to your 'calculations' page in Lexio one of two ways: 

  • Go to App —> Data —> Calculations 

  • Go to App —> Data —> Mapping —> Edit Calculations

Step 2 

    Click 'Create' to create a new calculation

Step 3

    Enter your formula and hit 'save'

  • Formulae should be structured as: 

    <mathematical operator> ((<aggregation><measure>), (<aggregation><measure>))

  • For example,

  • CPL = DIV(SUM(cost), COUNT(lead_id))

  • CPC = DIV(SUM(cost), COUNT(click_id))

  • Profit = SUB(SUM(revenue), SUM(cost))

  • Conversion Rate = DIV(SUM(demos), COUNT(deal_ID))

  • Pipeline Per State = DIV(SUM(amount), DISTINCT(state))    

  • Average Days to Close = AVG(DATEDIFF(resolution_date, open_date, granularity = day)

  • You can validate and verify your calculation via "Preview" 


Step 4
    Review how Lexio will write and style your calculated field in the “Semantic Attributes” tab
  • Lexio will auto-populate the expression for your field based on the formula provided
  • But you can always override expressions as needed, or click Revert Expressions to go back to the system generated expression.
  • Lexio will guess the styling type of your calculated field based on inputs, but you can override this
  • Lexio will also guess whether higher numbers are good, bad or neutral. This changes the colors up and down arrows that you see in the story. You can override this setting as well. 

Step 5

    Verify that your calculated field was created correctly in the 'Calculations' page

Step 6

    That’s it! Your calculated field should now automatically be available for use in your Newsfeed and Stories.

How do I edit a calculated field?

  • To start editing, click the pencil icon next to an existing calculated field:

  • To edit a formula, click anywhere into the formula to change to a different field, operator, or aggregation

  • To edit an expression associated with a calculated field, click into the ‘Semantic Attributes’ tab and into the Singular or Plural fields

  • To revert to Lexio generated expressions, simply click 'Revert Expressions

How do I delete a calculated field? 

  • Go to App —> Data —> Calculations
  • Locate the calculated field you want to delete and click the trash can

**Any associated Pinned Metrics + Notable Insights will automatically disappear

**Any bookmarks associated with that metric will remain but will revert to the original Lexio generated expressions

How do I edit analytical settings on my calculated field?

  • To edit settings like the default time frame comparison, the threshold for ‘notableness’, etc., submit a support ticket here!

Not supported today

  • Nesting previously created calculated fields in new calculated fields
  • Creating calculated fields from specialized Salesforce metrics (e.g. bookings, new pipeline, lost opportunities, etc.)