Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
m_perreault
Contributor III

Range Product Function

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.

 

 

DateReturn
1/31/20181.011010404
2/28/20181.005565826
3/31/20181.007899406
4/30/20181.0039712
5/31/20181.005528985
6/30/20181.005855925
7/31/20181.007522803
8/31/20181.007913107
9/30/20181.00652716
10/31/20181.002180224
11/30/20180.996697694
12/31/20180.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

 

 

Tags (3)
Labels (1)
1 Solution

Accepted Solutions

Re: Range Product Function

Try this

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

Re: Range Product Function

May be this

exp(RangeSum(Above(Log(Sum(Return)), 0, RowNo())))
m_perreault
Contributor III

Re: Range Product Function

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
Contributor III

Re: Range Product Function

@sunny_talwar 

 

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

 

Thanks

Re: Range Product Function

Try this

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

Re: Range Product Function

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)))

 

Re: Range Product Function

So, this works for you?

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

Re: Range Product Function

Right,

 

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

m_perreault
Contributor III

Re: Range Product Function

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

Re: Range Product Function

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

image.png