Sumifs formula to Alteryx

[KamenRider]

Hi and good day,

I would like to ask for your expertise about what is the equivalent of using sumifs in excel to Alteryx.

 

Please same excel file for your reference.

Thanks and hope to hear from anyone.

[rzdodson]

@KamenRider here is one approach using an analytic app.

[KamenRider]

Hi @rzdodson 

Thanks for the response, however use column X to perform for the sumifs.How did it get the 7%. for example. Also, I'm not familiar on how to attach the analytical app in my workflow. Please explain further.

Looking forward.

Kamen

[rzdodson]

@KamenRider unsure what your request is. If you are asking how your organization got to 0.07 for their weight, I would wager they utilized some expression that rounded to the nearest one-hundredth. On face, that would work for Records 1 and 5, but would not work for Records 2-4.

I also do not know what you mean by you want to "attach" an analytic application to a workflow. An Alteryx Analytic app is a completed workflow that enables Alteryx users to functionally swap components of their workflow (e.g. a fixed value in a formula) so they do not need to change the core logic of the workflow to achieve their intended results. Said differently, there isn't any "attaching" that occurs.

If you have a longer workflow that is a part of this process, please share. It is likely those tools and processes can be brought in to the app.

[KamenRider]

HI @rzdodson 

I apologize for the misunderstanding. The figures you are seeing on the Column X or Weight column in the excel file is the result of using the formula "Sumifs". I am hoping that using your expertise in determining the "Sumifs" formula using Alteryx, it will have same result as shown on Column X.

In line to the workflow you made, I am quite confuse with the lightning symbol found at the top of each Alteryx tools, Not sure how did you do it since it is my first time working/adding  it on my workflow. Please teach or advise me how you do it.

Kindly let me know if you have any questions.

Kamen

[rzdodson]

@KamenRider 

SUMIFS equivalent: there is not one within Alteryx. You will have to filter your data to your specifics, then run a Summarize tool on it that captures the sum of the numeric categories you are working with. In this use case, you'll then take that sum total, use an Append tool to get that value attached to the data set, then use a Formula tool to do the F2/SUMIFS section. 

The lightning symbols: when working with Alteryx Analytic Apps, the lightning bolt indicators reflect what can be connected to or changed. You can change static values in a Formula tool, change trade area radius (spatial), etc. Admittedly, I am opening up Pandora's Box a little bit, but there are a lot of great things you can do within Alteryx to get workflows tackling really complex use cases. You will also see lightning bolt anchors as soon as a Control Parameter tool is dropped on the canvas such as the case when working with Alteryx macros. Below are some helpful interactive lessons that will take you through both:

Alteryx Macros: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

Alteryx Analytic Apps: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%2...

As an aside, I would recommend going through macros first - there are a lot more common use cases for macros. Apps are great, but they are limited by Alteryx users being able to run them.

[KamenRider]

Hi @rzdodson 

Thanks for your explanation. It gives me new ideas and learning.

Going back to the workflow that you shared, I noticed that in the filter tool, we need to specify the date and I believe this will be a problem since the trade date appends or updates daily and if possible I don't to always add a filter tool for additional trade  date. Is there a way you could think of to change the it?

 

Thanks and looking forward for your response.

Kamen

[rzdodson]

@KamenRider based on how other developers would be interacting with this workflow, there a couple of options that immediately come to mind:

Option One: Use a Text Input tool to control the date(s) you are filtering to.

You'll have a Text Input tool that has a date placeholder in there. You can then either append that value to the data set as a new filed and then adjust your filter syntax so it is [MyDates]=[My Test Date] to return what you are needing. If you are needing this to be more dynamic (re: you need the test date to update automatically, you would insert a Formula tool after your Text Input tool and include syntax such as this (below) to update your date placeholder field:

DateTimeNow()

Or

DateTimeToday()

Option Two: Use a Date tool (Interface palette) to then update the date string in your Filter tool.

If in your use case you eventually get to the point when you are wanting to filter a range of dates, you would just replicate the steps herein to create start and end dates for your analysis. Attached is a sample of what I am referring to.