Filter Orders Value Greater Than 0 In Flow

[Koennn]

Hello!

I am trying to get a weekly sales report that shows the total orders with a value greater than 0.

I am able to get a count of all orders, but I can't figure out how to filter out orders with a value > 0.

The Flow looks like this

The Get Order Data query looks this

created_at:<='{{ scheduledAt }}' AND created_at:>'{{ scheduledAt | date_minus: "1 week" }} AND total_price:>0

The last part of the code does not seem to have any effect. I'm curious what's wrong with my query.

Koen

[paul_n · Accepted solution]

The queryable fields are listed here: https://shopify.dev/docs/api/admin-graphql/2023-01/objects/QueryRoot#connection-queryroot-orders

There does not appear to be a way to query by the order amount. As a workaround for that, you could do something like:
1. Order Created

2. if order total > 100

3. Add "Over 100" tag

Note: This only affects orders going forward (but you could run it manually on past orders). 

Then in your workflow, add this to the query:

tag:"Over 100"

If you need to total only orders over $100, I think the best way in a workflow to do that would be to handle it in the email body by looping over the orders and counting in liquid. Something like this code (not tested but close):

{% assign mysum = 0 %}
{% for order in getOrderData %}
   {% if order.currentTotalPriceSet.shopMoney.amount > 100 %}
         {% assign mysum = mysum | add: order.currentTotalPriceSet.shopMoney.amount %}
    {%endif %}
{% endfor %}
{{ mysum }}

[Koennn]

Hello Paul, thanks for this workaround! It works for now, hopefully in the future the query option will be added.

[BetterReports-K]

Hi @Koennn,

Keanna here from the Better Reports support team. I would also recommend our app Better Reports for this! 

We can create a custom weekly sales report filtered for orders with a total price > 0.  We can show individual order details or aggregate sales metrics for the week. This can be scheduled to be sent to you automatically at a set cadence (ex. Mondays at 8 am).

In addition to custom reporting capabilities, Better Reports offers more than 80 built-in reports that cover your most common reporting needs including sales, current inventory, refunds, payouts, and more. Our reports can even be scheduled to send at a set frequency to your email or Google Drive!

I encourage you to install Better Reports and start your free 14-day trial and I'll be happy to set this up for you.