Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
m_perreault
Creator III
Creator 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

 

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

15 Replies
sunny_talwar

May be this

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

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
Creator III
Creator III
Author

@sunny_talwar 

 

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

 

Thanks

sunny_talwar

Try this

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

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
Creator III
Creator III
Author

Right,

 

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

m_perreault
Creator III
Creator III
Author

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

image.png