How to do a SUMIFS with dynamic criteria in Alteryx

[craigedelman]

I've been looking for an answer to a problem on how to duplicate, in Alteryx, a rather simple SUMIFS excel formula but can't seem to find an answer on this.  My SUMIFS is slightly more complex as it's pointing to a dynamic criteria, maybe that is why.

I'm trying to duplicate the functionality in Alteryx of columns G and H of the excel.

If there is already a discussion that solves it and I missed it, please feel free to point me to that.

Thanks in advance!

[geraldo]

@craigedelman 

An workflow example.

the price column was transformed into number

[craigedelman]

that workflow has a batch macro that isn't connected.  Should this workflow run when just opening it and clicking run?

I get these errors when running it.

Start: Designer x64: Started running C:\Users\username\Downloads\Sumifs example dataset2.yxmd at 07/17/2023 13:43:04
Info: Designer x64: The Designer x64 reported: Running at a Low Priority.
Error: Designer x64: The Designer x64 reported: <EngineSettings> tag for ToolID="8" does not specify a plugin entry point and does not give a macro
End: Designer x64: Finished running Sumifs example dataset2.yxmd in 0.2 seconds with 1 error

[geraldo · Accepted solution]

@craigedelman 

Sorry, I sent it without the macro. now was the package

put a sort tool to sort the records by RecordID in the input form

[craigedelman]

Wow thanks! It works exactly like I needed.  Is the only way of achieving this with a batch macro?

I will try and implement this into the dataset I'm working with.  My source data is >5 million rows so we'll see how long this takes.  

[MK_Alteryx411]

Here you go!

Explanation for how the main points of this work:

1. The first Join: Here we join the initial data set to itself based on “Fruit”. What this does is connect data from each row for a given Fruit with every row creating every possible permutation of row combinations. For this data set we began with 2 Fruits: Apples and Cherries. Each Fruit had 16 rows of data, respectively. This means that for each of the 16 rows of Apples data there are 16 possible combinations for one row, thus 16x16 = or 256 rows will be generated for Apples, and likewise 256 for Cherries. Here we select two important columns to join on the right: The Snapshot Date Week and the Price Change (relabeled as Right_Price Change).
2. This enables us to calculate the delta of each snapshot date week for each of the 16 rows to determine and filter for snapshots within the 4 week restriction.
3. Once that is complete, grouping by Fruit and Snapshot Date and summing on “Right_Price Change”, we can determine the price changes for the last four weeks. The reason we sum on Right_Price Change vs. Price Change (from the left input), is based on the relationship to the actual dates of the price change occurring (right input) relative to whether it has the ‘change within the last 4 weeks’ relationship relative to the initial date set from the left input.

[craigedelman]

Hi MK_Alteryx411 --> Can you by chance upload the workflow file?