Range Product Function

[m_perreault]

Hi All,

I have data structured as the below.  I was wondering if it is possible to create a RangeProduct type function to calculate YTD,QTD,etc calculations.

Date	Return
1/31/2018	1.011010404
2/28/2018	1.005565826
3/31/2018	1.007899406
4/30/2018	1.0039712
5/31/2018	1.005528985
6/30/2018	1.005855925
7/31/2018	1.007522803
8/31/2018	1.007913107
9/30/2018	1.00652716
10/31/2018	1.002180224
11/30/2018	0.996697694
12/31/2018	0.992635858

So for example when I select 3/31/2018 I want my calculation for YTD to be 

Sum({<[Date] = {'3/31/2018'}>}Return) * Sum({<[Date] = {'2/28/2018'}>}Return) * Sum({<[Date] = {'1/31/2018'}>}Return)

Thanks!

Mark

[sunny_talwar]

oh okay, awesome!!

[vishalj88]

Hi Sunny,

Is this possible to do at the Script level? If not what is a similar function that can be written to execute it at the script level. Thanks.

Vishal

[sunny_talwar]

Should be doable in the script too.

[vishalj88]

Great. In that case, I'm looking to calculate a return metric that multiplies the prices days. In this very simplified example, I'm looking to multiply the last 3 days and this calculation must be grouped by product. I'm aware of obtaining the prior day's product using a series of peeks and in this ultra simplified example, i would have to call 3 peeks. However, in my actual problem that has time series data I have to multiple the prices of the last 240 days. Here it is rather inelegant to call 240 peeks, albeit will get the job done. I'm looking for a more elegant solution and tried to leverage above function, but that is only used in the UI. 

Can you help on how I should approach this? Thanks again.

Vishal

Product	Date	Price	Return
P1	1/1/2020	0.5
P1	1/2/2020	0.3
P1	1/3/2020	0.7	0.105
P1	1/4/2020	1.2	0.252
P1	1/5/2020	1.1	0.924
P2	1/1/2020	0.4
P2	1/2/2020	0.5
P2	1/3/2020	0.6	0.12
P2	1/4/2020	1.2	0.36

[sunny_talwar]

Use AsOfTable approach to do this... and Join the AsOfTable's date to the date table and then just use Multiplication....

Table:
LOAD * INLINE [
    Product, Date, Price
    P1, 1/1/2020, 0.5
    P1, 1/2/2020, 0.3
    P1, 1/3/2020, 0.7
    P1, 1/4/2020, 1.2
    P1, 1/5/2020, 1.1
    P2, 1/1/2020, 0.4
    P2, 1/2/2020, 0.5
    P2, 1/3/2020, 0.6
    P2, 1/4/2020, 1.2
];

TempCalendar:
LOAD DISTINCT Product,
	 Date
Resident Table;

Left Join (Table)
LOAD Product,
	 Date as AsOfDate,
	 Date(Date - IterNo() + 1) as Date,
	 -IterNo() + 1 as Num
Resident TempCalendar
While IterNo() <= 3 and Exists(Date, Date - IterNo() + 1);

DROP Table TempCalendar;

FinalTable:
LOAD Product,
	 AsOfDate as Date,
	 Only(If(Date = AsOfDate, Price)) as Price,
	 If(Min(Num) = -2, exp(Sum(Log(Price)))) as Return
Resident Table
Group By Product, AsOfDate;

DROP Table Table;

[vishalj88]

Wow, I bow to your greatness!!! Thanks Sunny.