Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Use Case: I want to use Zapier to read a query output, but only print the values that occurred in TODAY, LAST 7 DAYS, and LAST 30 Days.

Specific context: Imagine my query output has 2 columns, A: Date of Purchase and B: Total Purchase Amount. I want Zapier to print to slack and a few other things, "$XXX total was purchased today across Y orders, and $ZZZ total was purchased in the last 7 Days" or similar.

I am using a Zapier filter, but it seems to allow me to provide only a specific date (and time?) value, and not a generalized date like LAST30 (or today()-30 like I would in excel). See photo, where 'created date pacific' is the value i want to decision off-of (include those where created date is within last 7 or 30 days).

I am happy to go another way if there's a better way to do this.

zapier item.jpg


Idea (probably a bad one):

  1. Use =importdata to tether a google sheet to my DB query output.
  2. Once that data lives there, and is updated on a chron...
  3. Do my date filtering in there, and have Zapier just print those rolling 7 and 30 day totals to Slack and everywhere else I want?

Seems heavier-than-necessary, open to ideas...


Hi


https://community.zapier.com/discussion/211/how-to-use-dynamic-fields-in-filters

That being the case, just stick a "Formatter->Date/Time" step in before the filter to craft the date you want to filter on.


@AndrewJDavison_Luhhu Thank you so much! I was planning to spend an hour or so today bumbling through a code step to accomplish this, so you've literally saved my day :)

Thank you!


Haha - happy to hear it @Dylon!


@AndrewJDavison_Luhhu Sorry to circle back, but I think I'm still confused on how to use the filter step to accomplish what I want.

If I wanted to have Zapier ALWAYS return the sum of revenue booked on a specific date, or in the last 7 days, what do I need to provide in the filter step? I understand I can reference this item later, but my question is:

  • If I use the formatter step like you described, what should I be typing there to tell Zapier I care about items with a datestamp of TODAY, LAST 7 days, Last 30 days.
  • If today is 1/1/2020, and I tell Zapier to look for and sum-up the revenue associated to those purchases made in the last day, will Zapier know to treat 'today' (however Zapier wants me to reference this) dynamically? IE on Jan 2nd, will Zapier know to re-evaluate based on Jan 2, and not Jan 1?

Thanks again for any help you can provide here, hugely appreciated.


More importantly @Dylon, how are you planning to do the 'sum up' part of this by the way?



Was planning to either:

  1. Summarize in the DB query itself (this is a new #1 option after realizing Zapier may not help me sum specific values in the way I need). AKA use SQL to group the way I want (today, last 7, last 30).
  2. Or
  3. Use =importdata in google sheets and then throw a Zap on top of that.

I'm surprised I cannot summarize totals via Zapier with a dynamic filter, seems like a use-case many folks would desire.

If you think there is a better approach than either, I am very open to it. #1 is likely where I'll take this unless you think another approach is more fruitful.

Thanks again for being so willing to help me here, hugely appreciated!


Hi @Dylon

Nope - I think one of those options is good - I was just going to point out what you've discovered is that Zapier can't iterate over multiple bits of date within a single run. One of your solutions will do the trick.

That being the case though - I don't actually think my filter idea is going to work - because I see now you want to bring back all data points then filter those with a date that doesn't match the criteria - which you won't be able to do with Zapier.

So, I'm going to open this back up to the floor.


Thanks @AndrewJDavison_Luhhu got it. Thanks again, appreciate you lending your Zapier expertise.


Terms & Conditions