Cube.js This Week/This Month

Question

Is it possible to calculate some metrics (count, sum) for a specific time period like Today/This Week/This Month/Last Month in Cube.js. Is rollingWindow what I need? I tried it but it doesn't return me right data. Documentation is a bit confusing for me.

To be more descriptive I will use Orders table as an example.

I have simple Orders table in which I have product_id, product_name, created_at columns. On frontend i need to create analitycs table in which I will have product_name, this week (orders that are created this week for a specific product), this month (orders that are created this month for a specific product) and total orders by product.

Is there a way to do it like this:

measures: {
    thisWeek: {
      sql: 'id',
      type: 'count',
      filters: [{ sql: `${CUBE}.created_at = 'This week'` }],
    },
}

Answer 1

In case someone has similar problem, I managed to do it like this:

In schema:

measures: {
  thisWeek: {
    sql: `id`,
    type: `count`,
    filters: [
      {
        sql: `${CUBE}.created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)`,
      },
    ],
  },
  thisMonth: {
    sql: `id`,
    type: `count`,
    filters: [
      {
        sql: `${CUBE}.created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH)`,
      },
    ],
  },
},

Answer 2

Which configurations did you try with rolling_window and weren't working? The following should work and should give you the same value as wat you posted in your answer. But, you need to use "created_at" as your time dimension.

dimensions: {
    collector_date: {
      sql: `TIMESTAMP(created_at)`,
      type: `time`
    },
},
measures: {
  thisWeek: {
    sql: `id`,
    type: `count`,
    rolling_window: {
        offset: `end`,
        trailing: `7 day`,
    },
  },
  thisMonth: {
    sql: `id`,
    type: `count`,
    rolling_window: {
        offset: `end`,
        trailing: `1 month`,
    },
  },
},

Please tell me if this work!