Subtract from number variable in window function

I'm trying to create a query that allows the user to modify the rolling window using a variable. But, I'm getting a syntax error each time, so it appears that what I'm trying to do is not possible.

Here's what I'm doing:

SELECT date, sum(balance) over (
    partition by customer_id
    order by date
    rows between {{ months }} - 1 preceding and current row
) * (30 * {{ months }})
FROM tbl

As you can see from the query, I'm using the months variable in two different ways. And, instead of creating two variables, I'd like to be able to modify the variable either within the window or outside. Is this possible?

Hi @dpguthrie
Please post "Diagnostic Info" from Admin > Troubleshooting, and which database you are querying.
Which type of variable is {{months}} ?
You cannot use a Field Filter there - only a simple Text/Number/Date:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

Thanks @flamber. I was able to figure it out. I wasn't able to modify the variable within the window but was able to do it outside. I'm using Metabase with snowflake also. And, the {{ months }} variable is an integer.

@dpguthrie I don't think I understand. The Number variable would just insert a number in the query, so it should be the same if you manually wrote an integer instead of a variable.

But post "Diagnostic Info" from Admin > Troubleshooting
And post the full stacktrace you're getting from Admin > Troubleshooting > Logs.

I think it's a limitation on the Snowflake side, unless I'm just unaware of how to write the query. But, here's the info you're looking for:

Diagnostic Info:

{
  "browser-info": {
    "language": "en-us",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0.3 Safari/605.1.15",
    "vendor": "Apple Computer, Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_252-heroku-b09",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_252-heroku",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.252-b09",
    "os.name": "Linux",
    "os.version": "4.4.0-1088-aws",
    "user.language": "en",
    "user.timezone": "Etc/UTC"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "snowflake"
    ],
    "hosting-env": "heroku",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.6 (Ubuntu 12.6-1.pgdg16.04+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.35.4",
      "date": "2020-05-28",
      "branch": "release-0.35.x",
      "hash": "b3080fa"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Log:

[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:17:02-06:00 DEBUG metabase.middleware.log POST /api/card/200/query 202 [ASYNC: completed] 566.1 ms (4 DB calls) App DB connections: 0/7 Jetty threads: 2/50 (4 idle, 0 queued) (120 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:17:07-06:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 693.1 ms (4 DB calls) App DB connections: 1/7 Jetty threads: 2/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:20:14-06:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 539.7 ms (4 DB calls) App DB connections: 1/7 Jetty threads: 5/50 (3 idle, 0 queued) (119 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:23:12-06:00 DEBUG metabase.middleware.log GET /api/database/5/autocomplete_suggestions 200 13.8 ms (5 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (4 idle, 0 queued) (118 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:23:15-06:00 DEBUG metabase.middleware.log GET /api/database/5/autocomplete_suggestions 200 11.8 ms (5 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (4 idle, 0 queued) (120 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:23:17-06:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null {:database_id 5, :started_at (t/zoned-date-time "2021-04-27T21:23:16.303Z[Etc/UTC]"), :state "42000", :json_query {:database 5, :native {:query "
WITH

raw_data AS (
 SELECT
 end_of_month
 , sum(converted_balance) AS ending_receivables
 , sum(credit_sales) AS credit_sales
 FROM analytics_public.m_full_invoice_history
 WHERE company_id = {{ company_id }}
 
 -- Invoice Assignees
 [[ AND arrays_overlap(split({{ assignees__in }}, ','), invoice_assignees) ]]
 
 -- Invoice Tags
 [[ AND arrays_overlap(split({{ invoice_tags__in }}, ','), invoice_tags) ]]
 [[ AND (NOT arrays_overlap(split({{ invoice_tags__not_in }}, ','), invoice_tags) OR invoice_tags IS NULL) ]]
 
 -- Customer Tags
 [[ AND arrays_overlap(split({{ customer_tags__in }}, ','), customer_tags) ]]
 [[ AND (NOT arrays_overlap(split({{ customer_tags__not_in }}, ','), customer_tags) OR customer_tags IS NULL) ]]
 
 -- Subsidiary
 [[ AND array_contains(to_char(subsidiary_id)::variant, split({{ subsidiary__in }}, ',')) ]]
 [[ AND (NOT array_contains(to_char(subsidiary_id)::variant, split({{ subsidiary__not_in }}, ',')) OR subsidiary_id IS NULL) ]]
 
 -- Billing Country
 [[ AND array_contains(billing_country::variant, split({{ billing_country__in }}, ',')) ]]
 [[ AND (NOT array_contains(billing_country::variant, split({{ billing_country__not_in }}, ',')) OR billing_country IS NULL) ]]
 
 -- Currency
 [[ AND array_contains(currency::variant, split({{ currency__in }}, ',')) ]]
 [[ AND (NOT array_contains(currency::variant, split({{ currency__not_in }}, ',')) OR currency IS NULL) ]]
 GROUP BY 1
),

calculation AS (
 SELECT
 end_of_month
 , round(div0(
 ending_receivables,
 
 -- Only include rows where all periods have data
 CASE
 WHEN count(credit_sales) OVER (
 ORDER BY end_of_month
 ROWS BETWEEN {{ num_periods }} + 1 PRECEDING AND CURRENT ROW
 ) = {{ num_periods }} + 1 THEN sum(credit_sales) OVER (
 ORDER BY end_of_month
 ROWS BETWEEN {{ num_periods }} + 1 PRECEDING AND CURRENT ROW
 )
 ELSE NULL
 END
 ) * CASE
 WHEN {{ calc_type }} = 'actual' THEN sum(day(end_of_month)) OVER (
 ORDER BY end_of_month
 ROWS BETWEEN {{ num_periods }} PRECEDING AND CURRENT ROW
 )
 ELSE (30 * ({{ num_periods }} + 1))
 END, 0) AS DSO
 FROM raw_data
 ORDER BY 1
)

SELECT date_trunc('month', end_of_month) AS \"DATE\", DSO
FROM calculation
WHERE end_of_month [[BETWEEN {{ date_start }} AND {{ date_end }} --]] >= add_months(current_date, -13)
", :template-tags {:date_end {:id "1fc1f26e-7eba-c559-afc0-3cafb327c5fa", :name "date_end", :display-name "Date end", :type "date", :default nil}, :customer_tags__not_in {:id "0774ea16-ab22-c38f-779e-fcb49894aae9", :name "customer_tags__not_in", :display-name "Customer tags not in", :type "text"}, :billing_country__in {:id "c0494050-cc35-8236-6cef-41ea6a3e5ef4", :name "billing_country__in", :display-name "Billing country in", :type "text"}, :invoice_tags__not_in {:id "bc075144-b818-6ee5-b7f8-8eebabb770f5", :name "invoice_tags__not_in", :display-name "Invoice tags not in", :type "text"}, :assignees__in {:id "a604d917-d214-63c1-c53b-0cc03599311f", :name "assignees__in", :display-name "Assignees in", :type "text"}, :currency__in {:id "0f9aff5f-71c8-54c1-5ac0-c01382ee2a19", :name "currency__in", :display-name "Currency in", :type "text"}, :subsidiary__in {:id "a8a556c9-2465-80bc-d875-ac13eb567e5f", :name "subsidiary__in", :display-name "Subsidiary in", :type "text"}, :company_id {:id "d1c0c913-bc4d-8669-f98f-74ba022302a9", :name "company_id", :display-name "Company", :type "number", :required true}, :date_start {:id "f1d6dfa3-258c-d5cb-c7e9-ca306fa147d8", :name "date_start", :display-name "Date start", :type "date", :default nil}, :calc_type {:id "51053690-a1f1-0991-293f-3d4882e273ef", :name "calc_type", :display-name "Calc type", :type "text", :required true, :default "simple"}, :currency__not_in {:id "d7ae253e-67b4-030d-3d18-8daf7c201beb", :name "currency__not_in", :display-name "Currency not in", :type "text"}, :num_periods {:id "807569aa-95db-d2e6-9fe6-ed29ea6649cb", :name "num_periods", :display-name "Num periods", :type "number", :default "11", :required true}, :invoice_tags__in {:id "a577480f-bc5a-410d-73ac-5402c3f6d1b4", :name "invoice_tags__in", :display-name "Invoice tags in", :type "text"}, :subsidiary__not_in {:id "fa5ad352-e598-82e9-1919-13d59ecbaf59", :name "subsidiary__not_in", :display-name "Subsidiary not in", :type "text"}, :billing_country__not_in {:id "3c4aed4e-b07f-ae69-be43-02b9c599efd2", :name "billing_country__not_in", :display-name "Billing country not in", :type "text"}, :customer_tags__in {:id "a0f1dc69-36c2-1cbe-7837-59162d4da79d", :name "customer_tags__in", :display-name "Customer tags in", :type "text"}}}, :type "native", :parameters [{:type "category", :target ["variable" ["template-tag" "num_periods"]], :value "11"} {:type "category", :target ["variable" ["template-tag" "calc_type"]], :value "simple"} {:type "category", :target ["variable" ["template-tag" "company_id"]], :value "16"}], :middleware {:add-default-userland-constraints? true}}, :status :failed, :class net.snowflake.client.jdbc.SnowflakeSQLException, :stacktrace ["net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:151)" "net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:76)" "net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:495)" "net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:372)" "net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:575)" "net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:265)" "net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:203)" "net.snowflake.client.core.SFStatement.execute(SFStatement.java:874)" "net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:259)" "net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:181)" "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)" "--> driver.sql_jdbc.execute$fn__70879.invokeStatic(execute.clj:267)" "driver.sql_jdbc.execute$fn__70879.invoke(execute.clj:265)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:377)" "driver.sql_jdbc$fn__73052.invokeStatic(sql_jdbc.clj:49)" "driver.sql_jdbc$fn__73052.invoke(sql_jdbc.clj:47)" "query_processor.context$executef.invokeStatic(context.clj:59)" "query_processor.context$executef.invoke(context.clj:48)" "query_processor.context.default$default_runf.invokeStatic(default.clj:69)" "query_processor.context.default$default_runf.invoke(default.clj:67)" "query_processor.context$runf.invokeStatic(context.clj:45)" "query_processor.context$runf.invoke(context.clj:39)" "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)" "query_processor.reducible$pivot.invoke(reducible.clj:31)" "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__43149.invoke(mbql_to_native.clj:26)" "query_processor.middleware.check_features$check_features$fn__42463.invoke(check_features.clj:42)" "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__43314.invoke(optimize_datetime_filters.clj:133)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__47282.invoke(wrap_value_literals.clj:137)" "query_processor.middleware.annotate$add_column_info$fn__41084.invoke(annotate.clj:577)" "query_processor.middleware.permissions$check_query_permissions$fn__42338.invoke(permissions.clj:64)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__43813.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__42536.invoke(cumulative_aggregations.clj:61)" "query_processor.middleware.resolve_joins$resolve_joins$fn__44345.invoke(resolve_joins.clj:183)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__39271.invoke(add_implicit_joins.clj:245)" "query_processor.middleware.limit$limit$fn__43135.invoke(limit.clj:38)" "query_processor.middleware.format_rows$format_rows$fn__43116.invoke(format_rows.clj:81)" "query_processor.middleware.desugar$desugar$fn__42602.invoke(desugar.clj:22)" "query_processor.middleware.binning$update_binning_strategy$fn__41628.invoke(binning.clj:229)" "query_processor.middleware.resolve_fields$resolve_fields$fn__42144.invoke(resolve_fields.clj:24)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__38807.invoke(add_dimension_projections.clj:270)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__39027.invoke(add_implicit_clauses.clj:147)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__39420.invoke(add_source_metadata.clj:105)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__44010.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__41269.invoke(auto_bucket_datetimes.clj:125)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__42191.invoke(resolve_source_table.clj:46)" "query_processor.middleware.parameters$substitute_parameters$fn__43795.invoke(parameters.clj:97)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__42243.invoke(resolve_referenced.clj:80)" "query_processor.middleware.expand_macros$expand_macros$fn__42858.invoke(expand_macros.clj:158)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__39451.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__47166.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__44021$fn__44025.invoke(resolve_database_and_driver.clj:33)" "driver$do_with_driver.invokeStatic(driver.clj:61)" "driver$do_with_driver.invoke(driver.clj:57)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__44021.invoke(resolve_database_and_driver.clj:27)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__43064.invoke(fetch_source_query.clj:243)" "query_processor.middleware.store$initialize_store$fn__47175$fn__47176.invoke(store.clj:11)" "query_processor.store$do_with_store.invokeStatic(store.clj:46)" "query_processor.store$do_with_store.invoke(store.clj:40)" "query_processor.middleware.store$initialize_store$fn__47175.invoke(store.clj:10)" "query_processor.middleware.cache$maybe_return_cached_results$fn__42120.invoke(cache.clj:209)" "query_processor.middleware.validate$validate_query$fn__47184.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__43162.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__39289.invoke(add_rows_truncated.clj:36)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__47151.invoke(results_metadata.clj:128)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__42479.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__43884.invoke(process_userland_query.clj:136)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__42422.invoke(catch_exceptions.clj:174)" "query_processor.reducible$async_qp$qp_STAR___38090$thunk__38091.invoke(reducible.clj:101)" "query_processor.reducible$async_qp$qp_STAR___38090.invoke(reducible.clj:107)" "query_processor.reducible$sync_qp$qp_STAR___38099$fn__38102.invoke(reducible.clj:133)" "query_processor.reducible$sync_qp$qp_STAR___38099.invoke(reducible.clj:132)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:218)" "query_processor$process_userland_query.doInvoke(query_processor.clj:214)" "query_processor$fn__47350$process_query_and_save_execution_BANG___47359$fn__47362.invoke(query_processor.clj:233)" "query_processor$fn__47350$process_query_and_save_execution_BANG___47359.invoke(query_processor.clj:225)" "query_processor$fn__47394$process_query_and_save_with_max_results_constraints_BANG___47403$fn__47406.invoke(query_processor.clj:245)" "query_processor$fn__47394$process_query_and_save_with_max_results_constraints_BANG___47403.invoke(query_processor.clj:238)" "api.dataset$fn__50692$fn__50695.invoke(dataset.clj:52)" "query_processor.streaming$streaming_response_STAR_$fn__34128$fn__34129.invoke(streaming.clj:73)" "query_processor.streaming$streaming_response_STAR_$fn__34128.invoke(streaming.clj:72)" "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)" "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)" "async.streaming_response$do_f_async$fn__23118.invoke(streaming_response.clj:85)"], :context :ad-hoc, :error "SQL compilation error:
syntax error line 46 at position 36 unexpected '+'.
syntax error line 46 at position 40 unexpected 'PRECEDING'.
syntax error line 46 at position 54 unexpected 'CURRENT'.
syntax error line 47 at position 27 unexpected 'THEN'.
syntax error line 47 at position 35 unexpected '('.
syntax error line 47 at position 36 unexpected 'credit_sales'.
syntax error line 47 at position 50 unexpected 'OVER'.", :row_count 0, :running_time 0, :data {:rows [], :cols []}}

@dpguthrie Yeah, that error comes from Snowflake. If you cannot run the query via directly with JDBC Snowflake, then you'll have to report the problem to them. But you're using an old version of Metabase, and there has been driver updates since, so I would recommend trying a new Metabase version.

Thanks for taking a look, @flamber.