topic Re: SUMIFS Functionality Across Varying Time Dimensions in Alteryx Designer Desktop Discussions

SUMIFS Functionality Across Varying Time Dimensions

lhedrick — Tue, 18 Jun 2024 19:26:58 GMT

Hello Community, I've browsed through several threads related to replicating Excel's SUMIFS function in Alteryx, but haven't been able to find any solutions that seem to work for my use case. Essentially, I'm trying to calc year-to-date, quarter-to-date, month-to-date & week-to-date sales given a transaction date for each row (the period end date) and period beginning dates for year/quarter/month/week.

The attached file has the transaction date in column A, sales that need summed in column C, period beginning dates in columns H-K, and the summary calcs I need to produce in columns L-O (easy to do in Excel, but I can't quite seem to replicate in Alteryx).

I think the solution has something to do with utilization of filter/summarize/multi-row tools, but I'm struggling to find the right combo. Any help would be greatly appreciated!

Re: SUMIFS Functionality Across Varying Time Dimensions

apathetichell — Tue, 18 Jun 2024 23:03:37 GMT

Four different running total tools (one after another) - set your group by to your year/quarter/month/week start vaiables... rename fields after each runnint total tool.

Re: SUMIFS Functionality Across Varying Time Dimensions

Qiu — Wed, 19 Jun 2024 05:56:09 GMT

@lhedrick
same as @apathetichell with a quick sample as below.

Re: SUMIFS Functionality Across Varying Time Dimensions

lhedrick — Wed, 19 Jun 2024 12:43:41 GMT

Thank you @apathetichell and @Qiu! Just what I was looking for - knew there was a simple solution I just wasn't envisioning!

Re: SUMIFS Functionality Across Varying Time Dimensions

Qiu — Wed, 19 Jun 2024 22:56:12 GMT

@lhedrick
Good to know it works.
Actually your data is well formed that is why we can use just simple RunTotal tool.