Create 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

Divide one distinct count by another to calculate a rate between two types of entities, such as transactions per customer or events per device. COUNTD counts each distinct value once, even when it appears in multiple rows.

Example use-case

Compare order activity across sales channels by calculating orders per active store. Distinct counts prevent orders with multiple order lines from being counted more than once.

Source data: The orders dataset contains one row per order line, with order_id , store_id , and channel columns. Each order belongs to one store. Stores without orders in the selected data are not included.

How the calculation works

Formula : COUNTD(order_id) / COUNTD(store_id)

  1. Count orders: COUNTD(order_id) counts each unique order once.

  2. Count active stores: COUNTD(store_id) counts the unique stores represented in the selected data.

  3. Calculate the rate: Divide unique orders by active stores to get orders per active store.

Example result: 120 unique orders across 12 active stores gives 10 orders per active store .

Usage

  • Measure: In a column chart grouped by channel, shows orders per active store for each channel.

  • Having filter: Keep channels with more than 10 orders per active store using formula > 10 .

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'
});


const response = await client.create('formula',
  {
    expression: "COUNTD({<orders_dataset_id>:<order_id_column_id>}) / COUNTD({<orders_dataset_id>:<store_id_column_id>})",
    name: {
      en: "Orders per active store"
    },
    description: {
      en: "Unique orders divided by stores represented in the selected data"
    }
  }
);
Open in API Form
Example Response
200
400
500
{
  "count": 1,
  "rows": [
    {
      "created_at": "2024-05-20T12:44:23.163Z",
      "currency_id": null,
      "description": {
        "en": "Unique orders divided by stores represented in the selected data"
      },
      "ai_context": null,
      "expression": "COUNTD({<orders_dataset_id>:<order_id_column_id>}) / COUNTD({<orders_dataset_id>:<store_id_column_id>})",
      "format": ",.2af",
      "id": "a1ad615a-5199-4e58-8e0e-73dceba18af0",
      "informat": "numeric",
      "lowestLevel": 0,
      "name": {
        "en": "Orders per active store"
      },
      "subtype": null,
      "type": "numeric",
      "updated_at": "2024-05-20T13:29:37.273Z",
      "custom_metadata": null,
      "user_id": "34b22f36-4904-497f-8acf-b0ba1d7e83cb"
    }
  ]
}