Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic input ranges for formula calculations

Mj9715
8 - Asteroid

HI All, I have a month wise data set with two columns (A &B) which is the input data.

 

The output needs to be a 1month, 3month, 6month, 12month and YTD summary using a formula { =(PRODUCT(range/100+1)-1)*100}. Range denotes last three values of col A for a 3month calculation, last six values of col A for 6month calculation and so on. 

 

Both input and output in same tab of attached file.  Please can someone guide on this?

 

Input 

DateCol A Col B 
Jan-200.790.89
Feb-200.620.54
Mar-200.780.53
Apr-200.560.71
May-200.780.03
Jun-200.610.00
Jul-200.100.42
Aug-200.450.81
Sep-200.140.84
Oct-200.660.34
Nov-200.830.84
Dec-200.360.94
Jan-210.770.09
Feb-210.070.21
Mar-210.660.96
Apr-210.290.67
May-210.720.40
Jun-210.980.16
Jul-210.410.60
Aug-210.630.44

 

 

2 REPLIES 2
Christina_H
14 - Magnetar

There might be a more elegant solution, but I've used a series of sample/filter tools to identify the rows of interest for each different time period, then unioned them back together for the calculations.  My results don't quite match yours so I might have gone slightly wrong, hopefully you'll be able to spot where since I haven't found it yet...

 

Solution for product function taken from:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-mimic-Excel-PRODUCT-Function-mu...

 

Christina_Hurrell_0-1632126900254.png

 

Mj9715
8 - Asteroid

Thanks Christina! I will try this out, I think the output variation is due to the product function used in multi-row tool. 

Labels