Need Help on Calculation

[RaviPM10]

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

[patrick_mcauliffe]

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]

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]

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]

@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]

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]

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]

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]

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]

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.