Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.

Need Help on Calculation

RaviPM10
7 - Meteor

Hi All,

 

Very new to Alteryx and struggling to build some calculations.

 

I am trying to build a workflow which is tracking a stock performance for QTD & YTD

 

Each Month a new record will be added to the Input File & it should calculate recent month QTD & YTD return paste it to respective tab along with BMV & EMV.

 

Attached Raw file along with output which I'm looking out

 

Thank you for your help in advance

12 REPLIES 12
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey @RaviPM10 welcome to the wonderful world of Alteryx.

Its easier to help if you can attach a workflow of what you've tried so that no one is repeating something that didn't work.

 

 

RaviPM10
7 - Meteor

I was not able to calculate recent Quarter to date return & YTD return using =Sumproduct(product(B2:B7/100+1)-1)*100 for YTD

Same formula should be use for QTD calculation however it should be dynamic base on recent quarter

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Thanks @RaviPM10 

A few things I'd try:

1. Instead of getting the month as a string (which means you'd have to do an if/then/else for every month), I'd convert the string month number to a number so you can do "<= (month number) then Qx".

2. Then I would use a running total so it will continue to add them up and you can pick which line you want from that.  Group by Stock Name so it knows when to restart the running total.

3. To get the final value from each you could do a Max summarize on the running total for each group.

See the attached workflow.

 

 

 

 

RaviPM10
7 - Meteor

@patrick_mcauliffe Thanks for looking into it however I'm not looking out for running total, it should be calculate base on formula, in excel we are using =sumproduct(product(range of returns/100+1)-1)*100 kindly refer the excel attached file for quick validation. also for recent QTD it should create a separate tab & for YTD too it should create new tab. it would be great help if help me to resolve this soon.

 

in simple Math's the formula to calculate is QTD & YTD is same only range would vary ((1+B2/100)*(1+B3/100)*(1+B4/100)*******-1)*100 

 

 

 

 

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey @RaviPM10 , my apologies I missed that.  Also the PRODUCT function is new to me but I'll see if I can learn it.

Check out the attached where I use the multi-row to identify arrays, similar to how its done in Excel.

The last number of each quarter is correct-ish, so let me know what you think of this.

 

 

RaviPM10
7 - Meteor

Hi @patrick_mcauliffe I'm not able to open workflow its seems you 're using updated version, I'm using old version however i was able solve this problem kindly find attached workflow which I created.

 

Now I want to filter only latest quarter for e.g if in file latest data for Aug 2022 then it should select only July & august data. 

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey @RaviPM10  try this version with your Book5 file.

We solved it in a very similar way.  Mine does have what you'd need to pick the latest data for each.  You'd just add a filter at the end so that the tile seq number = the last row for each group.

All I did with the attached file was downgrade my previous one.  If that doesn't work, then let me know which version you're on so I can downgrade to that version.

RaviPM10
7 - Meteor

Thanks @patrick_mcauliffe this looks more streamline in out put I'm trying to get only latest Quarter data for e.g. in our calculation file in output its should only reflect Q2 and in future if Q3 adds then it should only reflect Q3 data in output. I just added one more column in data as Date.

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey @RaviPM10  you could use the exact same workflow with two modifications:

1. Add a Sort right after the Data Input tool.  You would want it to be a sort on Stock, the Date (both Ascending).  That way the Tile tool will be sure to set the sequence correctly.

2. Add the very end, add a Sort By and then a Unique tool.  Sort by Stock (Ascending), then Date (Descending).  For the Unique Tool, you'd just need to select Stock as your Unique field.