Dashboard interval (between) parameter with raw query parameter

I have a dashboard where I can select through a parameter an interval as a filter for all the queries. Dashboard name “new customers performance” and widgets like: how many orders we had from new customers in a given period of time, how many distinct customers, average order value for these customers etc.

the schema for order has something like this:

  • order_id
  • coupon_id

I need a widget to count how many order_ids are there where coupon id is not a certain value or it’s empty (Basically, these customers are new but they didn’t use one incentive we are offering to new customers).

I wasn’t able to build the query with the query builder, because if I use this setup:
image
then the query generated will be:

`promotion_id` <> 132
   AND `promotion_id` IS NULL

what I need in fact is

(`promotion_id` <> 132
   OR `promotion_id` IS NULL)

observe the parenthesis (as there are other conditions in the query that must be cumulated with AND, only these two, related to promotion, should be with OR.

now, I tried to do a native SQL query, but I don’t know how should I put the parameter in the query so I can get queries like this:

(`promotion_id` <> 132
   OR `promotion_id` IS NULL) and completed_at between ? and ? 

where between ? and ? should be a parameter coming from the dashboard filter configuration.

any help is much appreciated.

Hi @gabiudrescu

Yeah, currently the QB doesn’t support advanced queries like that, so your right choice would be doing it in SQL.
You need to use Field Filters as the variable type:

(`promotion_id` <> 132 OR `promotion_id` IS NULL) AND {{CompletedVariable}}

similar question: Dashboard raw query with filter not working

@gabiudrescu
Not quite similar. The question you’re linking to is a problem with the Data Model not being defined correctly.