How can I calculate metrics (count, sum) for specific time periods like Today/This Week/This Month/Last Month in Cube.js? I attempted to use rollingWindow but it did not provide me with the correct data. The documentation is a bit unclear to me.
For a clearer example, let's consider an Orders table.
Imagine I have a simple Orders table with product_id, product_name, and created_at columns. On the frontend, I want to create an analytics table that includes product_name, orders created this week, orders created this month, and total orders by product.
Is there a way to achieve this, like so:
measures: {
thisWeek: {
sql: 'id',
type: 'count',
filters: [{ sql: `${CUBE}.created_at = 'This week'` }],
},
}