Replicate PRODUCT excel formula for select period and entire period
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Help
- Macros
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Mysa,
I've updated the workflow to include all three Reqs. Let me know what you think!
Cheers,
Shifty
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Coming back to what PRODUCT formula does, I'm uploading breakdown of formula in a revised excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Great solution @Shifty !
