topic Re: Need Help on Calculation in Alteryx Designer Cloud Discussions

Need Help on Calculation

RaviPM10 — Wed, 12 Jul 2023 10:52:50 GMT

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

Re: Need Help on Calculation

patrick_mcauliffe — Wed, 12 Jul 2023 12:29:32 GMT

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.

Re: Need Help on Calculation

RaviPM10 — Wed, 12 Jul 2023 12:55:45 GMT

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

Re: Need Help on Calculation

patrick_mcauliffe — Wed, 12 Jul 2023 13:48:40 GMT

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.

Re: Need Help on Calculation

RaviPM10 — Wed, 12 Jul 2023 16:46:12 GMT

@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

Re: Need Help on Calculation

patrick_mcauliffe — Thu, 13 Jul 2023 15:54:29 GMT

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.

Re: Need Help on Calculation

RaviPM10 — Thu, 13 Jul 2023 17:00:40 GMT

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.

Re: Need Help on Calculation

patrick_mcauliffe — Thu, 13 Jul 2023 17:57:56 GMT

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.

Re: Need Help on Calculation

RaviPM10 — Fri, 14 Jul 2023 08:35:29 GMT

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.

Re: Need Help on Calculation

patrick_mcauliffe — Fri, 14 Jul 2023 12:12:34 GMT

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.

Re: Need Help on Calculation

RaviPM10 — Fri, 14 Jul 2023 12:34:05 GMT

Hi @patrick_mcauliffe I'tried that way however its only showing 06/30 and I would need 05/31 & 04/30 along with June data.

Re: Need Help on Calculation

RaviPM10 — Mon, 17 Jul 2023 12:34:02 GMT

@patrick_mcauliffe Just checking have you got chance to look onto how to filter last quarter data in file in a dynamic way.

Re: Need Help on Calculation

patrick_mcauliffe — Wed, 02 Aug 2023 14:08:55 GMT

Hey @RaviPM10, sorry I was out for a bit. Did you figure this out?

As long as the dates you have in Excel are the correct dates for each period this should work fine.

If not, then what region is your Excel set to?
File --> Options --> Regional Format Settings