How do I query for a measure as a single calculated value (10 days average) and not separated by granularity (daily measure)?

Question

I have a doubt. I currently have a cube with a preAggregation defined with day granularity and some measures A, B, C. The preAggregation works fine when I query using timeDimension with a fixed dateRange and granularity (it retrieves a time series that I can plot in a chart, so everything is ok to this point).

My problem is that I also want to show these measures (A, B, C) averaged as a single value, but if I remove the granularity from the timeDimension, the cube stops using the preAggregation, and instead it performs the full query.

Summarizing:
I need a to retrieve a measure (such as sales $$$) for each day of a fixed time range (separated as a series), and I also need the monthly average (as a single number). How do I build the second query (single value measure)? For the first one, I simply add the following timeDimension:

{
   "granularity": "day",
   "dimension": "timestamp",
   "dateRange": [
      "2020-01-26T00:00:00Z",
      "2020-02-06T00:00:00Z"
   ]
}

But when I want the measure aggregated for the same dateRange as a single value, I don't know how to build the query.

Answer 1

All avg measures are considered Not Additive and it's why most of rollups that works for Additive ones aren't selected for queries containing avg measures: https://cube.dev/docs/pre-aggregations#rollup-rollup-selection-rules.

However there's a trick for average measures that allows to reclassify Not Additive measures to Leaf Measure Additive. For example you can rewrite

cube(`Cube`, {
  // ...

  measures: {
    averageA: {
      // ...
      type: `avg`
    },

    averageB: {
      // ...
      type: `avg`
    },

    // ...

  },

  // ...

  preAggregations: {
    averagesRollup: {
      type: `rollup`
      measureReferences: [averageA, averageB, averageC],
      timeDimensionReference: timestamp,
      granularity: `day`
    }
  }
})

to:

cube(`Cube`, {
  // ...

  measures: {
    A: {
      // ...
    },

    B: {
      // ...
    },

    // ...

    count: {
      type: `count`
    },

    averageA: {
      sql: `${A} / ${count}`,
      type: `number`
    },

    averageB: {
      sql: `${B} / ${count}`,
      type: `number`
    },
    // ...
  },

  // ...

  preAggregations: {
    averagesRollup: {
      type: `rollup`
      measureReferences: [A, B, C, count],
      timeDimensionReference: timestamp,
      granularity: `day`
    }
  }
})

In this case Cube.js knows that averageA, averageB and averageC measures are composed out of A, B, C and count and will use averagesRollup as in Leaf Measure Additive query and return results even if time dimensions granularity isn't set.