1

I want to create a calculated measure amountPercentage in CubeJS. I have an amount measure and I want to check how many percentages from the whole amount (sum for all records in response) is in every single record.

Let's say I have Products dimension: Shoes, Jackets. Shoes amount = 30, Jackets amount = 120. The measure amountPercentage should be 20% for shoes.

Any way to achieve that?

Krystian Fras
  • 91
  • 1
  • 2
  • 4

1 Answers1

1

Best practice for such kind of queries is to handle this on the client side and split it into separate queries. Typical queries for such use case are:

  1. Fetch absolute amounts for top 10:
{
  dimensions: ['Products.name'],
  measures: ['Sales.amount'],
  timeDimensions: [{
    dimension: 'Sales.time',
    dateRange: 'Last 30 days'
  }],
  limit: 10
}
  1. Fetch total amount:
{
  measures: ['Sales.amount'],
  timeDimensions: [{
    dimension: 'Sales.time',
    dateRange: 'Last 30 days'
  }]
}
  1. Fetch other than top 10 products total amount:
{
  measures: ['Sales.amount'],
  filters: [{
    member: 'Products.name',
    operator: 'notEquals',
    values: [...top10ProductNames]
  }],
  timeDimensions: [{
    dimension: 'Sales.time',
    dateRange: 'Last 30 days'
  }]
}

Then percentages are calculated on the client side.

Pavel Tiunov
  • 1,163
  • 6
  • 8
  • Is this still correct advice? Doing such calculation client side defeats the purpose of using a headless BI & metrics store. – dendog Sep 03 '22 at 16:48
  • @dendog A bit late to the game, but [dynamic measures seem to be the way to go](https://cube.dev/docs/recipes/referencing-dynamic-measures). You would have to programmatically compute the `Products` dimension (or hardcode it) – jugo Nov 29 '22 at 13:22
  • @dendog Still correct advice for Headless BI approach. Cube SQL API uses multiple queries and post-processing for complex SQL queries like these. – Pavel Tiunov Dec 03 '22 at 21:58
  • @PavelTiunov I think using plain SQL is a better development experience in that case - less error prone. – dendog Dec 06 '22 at 19:21