nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to calculate Average contribution for last 30 days on the account-security ID-date

anonymous008_G

Hi,

I need someone to help to calculate average contribution return on the account-security id-date level. In my dataset i have multple accounts, their respective multiple securities, and multple dates. my requirement is if first row has data like acct 123, security id 10001, date 03-03-2026, daily contribution is 0.40 so tool should calculate average contribution return for the date i.e 03-03-2026 for the combination of account+security id. if second row has different date then it should calculate average value based on that date for last 30 days ans so on.

Help

Accepted answers

jrlindem

I agree, @OTrieger with teaching concepts vs providing the full solution 🙂. Does make for better learning. To that end... @anonymous008_G here's the additional piece that you'll need to consider to solve your issue.

The first part (above) is the aggregation part. Plenty of information on Summarize Tool and it's a basic building block that you'll need to learn how to use effectively.

The second part, which is getting your lag on the dates, can be accomplished in a number of ways, but filtering on the Max(Date), in your case March 5 for all dates within -30 days give you a rolling period of time. Once you do that, you can feed that filtered data into your Summarize Tool to aggregate the results.

And while I realize this might not be exactly what your use case is calling for, the concepts are there for you to put together and merge into a solution.

Regarding your original "groupings", you can take this same concept and apply the lagging logic to solve for multiple dates using the Multi-Row-Formula Tool to identify each of the lagging periods. Instead of having one Max(Date), you would flag each new interval and then join those "max" dates back to your original data and then create the lag from there....

Hope this helps, please mark any/all of the responses as solutions that helped you get to your final workflow! -Jay

All comments

OTrieger

@anonymous008_G
Have you tried to use Summarise tool, as it can average values and if you will group it on date then you will get what you are looking for

jrlindem

As @OTrieger points out, using the Summarize Tool and grouping on the [Accounts], [Securities], and [Date] fields is the way to go. Then choose your field to aggregate and the aggregation type. It'll perform the aggregation across that grouping of fields and then reset and aggregate again on the next set within the groupings.

If you wanted to provide some fake data, we can build out a mock-up workflow to show it. -Jay

anonymous008_G

@jrlindem - it would be really helpful if could give me some sample workflow with fake data. That will make my understanding much better.

OTrieger

@anonymous008_G
Check Alteryx Academy there is a video that explains how to use and configure the Summarise tool. No need to built a sample workflow, it is simply configuration of the tool itself.

jrlindem

Pretty simple to do. It would be best to use sample data from your input schema, but here's something basic to get you started:

Workflow is also attached. -Jay

jrlindem_example.yxmd

anonymous008_G

Hi - @jrlindem & @OTrieger both of you I really appreciate your response on this. But maybe I had not explained my requirement correctly. Below calculation for average contribution would not work in my case since my requirement is if first record date is 03/05/2026 then tool should calculate average contribution for last 30 days record for the combination of account and security ID. Having said that average contribution calculation considers the data for last 30 days prior to the date of 03/05/2026. I have attached excel file with sample data, could you please confirm if it is doable in alteryx for multiple accounts and securities?

Sample Data.xlsx

jrlindem

It is possible, you'll need to use the multi-row-formula tool to flag the groupings that you want based on the Record Date. Then you can run it through the Summarize Tool. If @OTrieger doesn't beat me to it, I can help build something out later today. -Jay

anonymous008_G

@jrlindem thank you, I will be waiting for your solution.

OTrieger

@jrlindem 😂
I believe that solving someone else problem is not the way to go. Helping and guiding is the way as it requires the person to deal with the problem and understand. Specially when it comes to understand how to configure tools, as there is so many videos on Alteryx Academy.
I'm trying to evaluate Am I going to help him or if I will do the work for him will he learn from it? It seems to me that people here are taking advantage of the fact that others are wants to help.

What will be a better help, giving someone a fish or teach him how to catch fish? 🤔

jrlindem

The first part (above) is the aggregation part. Plenty of information on Summarize Tool and it's a basic building block that you'll need to learn how to use effectively.

And while I realize this might not be exactly what your use case is calling for, the concepts are there for you to put together and merge into a solution.

Hope this helps, please mark any/all of the responses as solutions that helped you get to your final workflow! -Jay

anonymous008_G

Hi @OTrieger - I completely agree with your view. I am not expecting the community to do my work. I am here for the solution. Before approaching to the community members, i did try multiple times by taking ideas from videos with the help of AI. Now again, I did try implenting the @jrlindem approach using the multi-row-formula tool, but still the end value doesn't match when I calculate manually. Maybe I am doing something wrong. If you guys have any material that would help me here, please share the link or document..

anonymous008_G

Hi @OTrieger – I am completely agree with your view. I am not expecting someone to do work on my problem, I am simply expecting solution for my problem here since you guys are expert in these matters. Before asking in the community I search lot of videos, did try with AI based solutions, even now I have also tried to implement @jrlindem approach but end value doesn’t match with manual calculations for Account+security combination. Below some screenshots of the workflow, if could guide me to the right direction if I’m doing anything wrong.