Skip to content

Creating measures based on period length and query granularity #8768

@ghost

Description

Problem

How can I retrieve the timeDimension period and granularity for which the query is made?

To compute certain temporal averages, I need to divide measures by the period length in days.

The grain of my table is user x day (one row per day per active user). Initially, I created a countDistinct measure based on the date field. However, for a given period, when filters are applied, if all users are filtered out on a particular day, that day is not counted.

`SELECT 
*,
${FILTER_PARAMS.[Cube].timestamp.filter((from, to) => { return `DATE_FROM(DATE(${to}), DATE(${from}), DAY)`})} as period_in_days,
FROM ...`

I tried to create a field when defining my cube, by building a dimension and then a measure on period_in_days, but it doesn't work when the query has a grain. period_in_days still equals to the entire period, regardless of the granularity.

Metadata

Metadata

Assignees

No one assigned

    Labels

    questionThe issue is a question. Please use Stack Overflow for questions.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions