topic Re: Range Product Function in App Development

Range Product Function

m_perreault — Sat, 16 Nov 2024 06:14:05 GMT

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

Re: Range Product Function

sunny_talwar — Wed, 27 Mar 2019 16:42:29 GMT

May be this

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

Re: Range Product Function

m_perreault — Wed, 27 Mar 2019 16:51:14 GMT

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

Re: Range Product Function

m_perreault — Wed, 27 Mar 2019 23:16:45 GMT

@sunny_talwar

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

Thanks

Re: Range Product Function

sunny_talwar — Thu, 28 Mar 2019 11:16:35 GMT

Try this

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

Re: Range Product Function

m_perreault — Thu, 28 Mar 2019 15:29:41 GMT

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

sunny_talwar — Thu, 28 Mar 2019 15:17:50 GMT

So, this works for you?

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

Re: Range Product Function

m_perreault — Thu, 28 Mar 2019 15:30:04 GMT

Right,

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

Re: Range Product Function

m_perreault — Thu, 28 Mar 2019 15:45:44 GMT

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

sunny_talwar — Thu, 28 Mar 2019 15:48:25 GMT

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

Re: Range Product Function

sunny_talwar — Thu, 28 Mar 2019 15:49:02 GMT

oh okay, awesome!!

Re: Range Product Function

vishalj88 — Wed, 15 Jan 2020 22:13:18 GMT

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

Re: Range Product Function

sunny_talwar — Mon, 27 Jan 2020 17:07:02 GMT

Should be doable in the script too.

Re: Range Product Function

vishalj88 — Mon, 27 Jan 2020 17:55:41 GMT

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

Re: Range Product Function

sunny_talwar — Mon, 27 Jan 2020 18:44:44 GMT

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;

Re: Range Product Function

vishalj88 — Mon, 27 Jan 2020 21:24:38 GMT

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