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]

May be this

exp(RangeSum(Above(Log(Sum(Return)), 0, RowNo())))

[m_perreault]

Thanks Sunny, this looks like it would work in a table but I am looking to create a few KPI Objects (one for YTD, QTD,etc.)   Do you know how you could apply this formula there?

Thanks,
Mark

[m_perreault]

@sunny_talwar 

Any thought on how this would be possible in a KPI?

Thanks

[sunny_talwar · Accepted solution]

Try this

=exp(Sum({<Date = {"<=$(=Date(Max(Date), 'M/D/YYYY'))"}>}Log(Return)))

[m_perreault]

Does not seem to be working.  I get the correct sum of returns using below formula 

Sum({<[CPDate],Date = {">=$(=QuarterStart(Max(Date)))"}>}Log(PeriodPL))

however when I add the exp() function my KPI returns null

Exp(Sum({<[CPDate],Date = {">=$(=QuarterStart(Max(Date)))"}>}Log(PeriodPL)))

[sunny_talwar]

So, this works for you?

Sum({<[CPDate],Date = {">=$(=QuarterStart(Max(Date)))"}>}Log(PeriodPL))

[m_perreault]

Right,

But when I add the exp() function around it I return null in my KPI

[m_perreault]

Hi Sunny,

This works the issue was my values for return were wrong (over 100) so the exp function was becoming too high a number for Qlik to recognize.

Thanks,

Mark

[sunny_talwar]

Not sure, but seems to work for me based on the sample provided