Replicate PRODUCT excel formula for select period and entire period

[Mysa]

Hi 

Please help me in replicating PRODUCT excel formula. Req1 column is Cumulative for each attribute, Req2 is Cumulative of 3 months, Req 3 is YTD Cumulative. Also attaching the excel. 

Thanks in Advance.

 

[Shifty]

@Mysa 

This is now doubt a fiddly one (for me at least but I'm no expert!).  It's pretty much achievable with the Multi-Row Formula. I've attached a workflow that addresses Req1 and Req3.  I'm still working on Req2 for you!

Take a look and let me know if that does tick off Req 1 and 3 for you.

Shifty

[Shifty]

Hey @Mysa,

I've updated the workflow to include all three Reqs.  Let me know what you think!

Cheers,

Shifty

[Mysa]

Hi Shifty,

Thanks for the efforts. the PRODUCT formula in excel essentially does Cumulative Return, not a cumulative total. 

However your seems good. 

Also you ignored Attribute1 column the calculation should also consider AA and AB in that column seperately.

[Shifty]

Ahh I see what you mean, if you want Req1 to group by Attribute1 (and essentially reset for AB) then you can just check the Group by Attribute1 in the first Multi-Row Formula tool.

[Mysa]

Coming back to what PRODUCT formula does, I'm uploading breakdown of formula in a revised excel. 

[Shifty]

@Mysa,

There has been another post about cumulative return: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculation-of-cumulative-returns/td-p...

Maybe your understanding of the process together with that post will get you to the solution you're looking for.

Shifty

[Shifty · Accepted solution]

Hi @Mysa,

Please see the attached workflow.  I'm fairly certain that the output columns match your expectations now (i.e. I've managed to wrap my head around what you were using the PRODUCT function for in Excel). 

There are now three additional columns added called Req1 (B), Req2 (B) and Req3 (B) that match the corresponding Req columns from your sample file.

There's no denying I've made a meal of it but I think (I hope) I got there in the end. 🤣

Shifty

[IraWatt]

Great solution @Shifty !