Metric Views Window Period to Date not working

[jroots]

The documentation gives an example of a period to date measure. The intention is to give the sum (in this example) of orders in the current year to date. However, what actually happens is that the measure returns the result for the most recent period for which there are entries. If the current year has no rows, it returns the result for the most recent year which does. (There is a workaround, which is to use a filter with current_timestamp(), but then materialized views don't work.)

See results and measure definition attached. The measure definition is taken directly from the referenced documentation.

[Ashwin_DSA · Accepted solution]

Hi @jroots,

On some research, I can see that the YAML in the docs is doing what window measures are defined to do, but the wording is perhaps a bit misleading. In the below example you shared,

range: current means "rows whose order value equals the current row’s value,"...

and semiadditive: last says "when that order dimension (here year and date) isn’t grouped, use the last value in that window.”

When you query MEASURE(ytd_sales) without grouping by year or date, the engine selects the last date from the last year in the data and returns that year-to-date total. If no data for the actual calendar year contains current_date(), it naturally falls back to the most recent year that does have rows.

To get "YTD for the calendar year containing current_date()," you currently have to push the notion of "current year" into the query instead of the metric view definition, for example, by filtering on DATE_TRUNC('year', date) = DATE_TRUNC('year', CURRENT_DATE()). Putting CURRENT_DATE() / CURRENT_TIMESTAMP() directly in the metric view YAML makes the definition invoker-dependent, which is why materialized metric views then stop working (they can’t be reused if the definition depends on evaluation time).

Does this give you a direction?

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[jroots]

Hi Ashwin. Thanks for looking into this. Your description of the behaviour and the options matches my understanding, but from my perspective this is a defect: I don't think there's a reasonable interpretation of "period to date" (which is the documented capability) as actually meaning "a full period from beginning to end where the period is the last one for which there is data".

Regarding the CURRENT_DATE() workaround, metrics materialized views can be refreshed as frequently as every hour. I understand that materialization doesn't make sense when there is a genuine dependency on a value that is unknown when the materialized data is calculated, but that is not the case in this example, as we are using "current" time at a granularity three orders of magnitude more coarse than this.

I will use the workaround for now, but is there a process for getting this raised with product / engineering?

Thanks,

Jamie

[Ashwin_DSA]

Hi @jroots,

Thank you for your feedback. I have flagged this issue internally for clarification and will let you know as soon as I receive more information.

In the meantime, feel free to provide feedback on the documentation page. You should see an option on the right side of the window to do so.

Please keep in mind that this is an experimental feature.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.