About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

Best answer

How to use Zapier to report totals for "rolling dates" like "last 7 days"

  • 20 February 2020
  • 9 replies
  • 872 views
D
Userlevel 1

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


icon

Best answer by AndrewJDavison_Luhhu 20 February 2020, 14:42

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

9 replies

D
Userlevel 1

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...


AndrewJDavison_Luhhu
Userlevel 7
Badge +10

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.


We're Zapier Certified Experts - we help businesses do cool stuff with automation | www.luhhu.com
D
Userlevel 1

@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!


AndrewJDavison_Luhhu
Userlevel 7
Badge +10

Haha - happy to hear it @Dylon!


We're Zapier Certified Experts - we help businesses do cool stuff with automation | www.luhhu.com
D
Userlevel 1

@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.


AndrewJDavison_Luhhu
Userlevel 7
Badge +10

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



We're Zapier Certified Experts - we help businesses do cool stuff with automation | www.luhhu.com
D
Userlevel 1

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!


AndrewJDavison_Luhhu
Userlevel 7
Badge +10

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.


We're Zapier Certified Experts - we help businesses do cool stuff with automation | www.luhhu.com
D
Userlevel 1

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


Terms & Conditions