Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

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
Top Solution Authors