Validate Formula

LLM-friendly URL
POST
https://api.luzmo.com/0.1.0/formula
API call form
Examples
List of examples
  • Calculate a ratio of distinct counts with COUNTD
  • Calculate a score from conditional counts with IF, COUNT, and COUNTR
  • Compare group averages with the overall average using GROUPFULL
  • Compare averages with a broader group using GROUPINEX
  • Average per-entity totals with nested SUM, GROUPINEX, and AVG
Description

Use IF inside COUNT to count rows that meet selected conditions, then combine those counts and divide by the total row count from COUNTR . This pattern supports metrics based on the difference between two categories relative to all rows.

Example use-case

Track customer willingness to recommend your service with Net Promoter Score (NPS), calculated from the balance of promoters and detractors.

Source data: The surveys dataset contains one row per response, with score and response_date columns. Every response has a non-null integer score from 0 to 10.

How the calculation works

Formula : (COUNT(IF(score > 8, 1, null)) - COUNT(IF(score < 7, 1, null))) * 100 / COUNTR()

  1. Count promoters: COUNT(IF(score > 8, 1, null)) counts responses scoring 9 or 10. IF returns 1 for matches and null otherwise. COUNT counts the non-null values.

  2. Count detractors: COUNT(IF(score < 7, 1, null)) counts responses scoring 0 to 6 in the same way.

  3. Count all responses: COUNTR() includes every response, including passive scores of 7 or 8.

  4. Calculate NPS: Subtract detractors from promoters, divide by all responses, and scale to the NPS range of -100 to 100. Use numeric formatting for this score.

Example result: 60 promoters and 20 detractors among 100 responses gives an NPS of 40 .

Usage

  • Measure: In a column chart grouped by response month, shows monthly NPS.

  • Having filter: Keep months with more detractors than promoters using formula < 0 .

Install
npm install @luzmo/nodejs-sdk
Example Request
Shell Node Java .NET Python PHP 
import Luzmo from '@luzmo/nodejs-sdk';
const client = new Luzmo({
  api_key: '<your Luzmo API key>',
  api_token: '<your Luzmo API token>',
  host: 'https://api.luzmo.com'
});
Open in API Form
Example Response
200
400
500
{
  "type": "numeric",
  "valid": true
}