
1. Re: Time series data calculation
Diego Parker May 20, 2019 10:20 AM (in response to Jeanne Sun)Hi Jeanne,
Please find the solution attached.
The formula is:
(IF [Fund Name Parameter]=[Fund Name]
THEN
[Cost]
END)

(IF [Fund Name Parameter]=[Fund Name]
THEN
{FIXED [Sector]: MIN([Cost])}
END)
If this helps you or answer your question, please mark it as helpful/correct so other users can refer to it. Thanks!
Best,
Diego

Example.twbx 20.8 KB


2. Re: Time series data calculation
Jeanne Sun May 22, 2019 12:48 AM (in response to Diego Parker)The second part of the formula doesn't seem right, as I would like to lookup the lowest paying fund that is not the selected fund but within the same sector as the selected fund.

3. Re: Time series data calculation
Diego Parker May 22, 2019 1:38 AM (in response to Jeanne Sun)That is what the fixed is doing.
(IF [Fund Name Parameter]=[Fund Name] // If the parameter is equal to the fund name
THEN
{FIXED [Sector]: MIN([Cost])} // For that sector give me the minimum cost.
END)
(0.70)
In the picture you see I have selected "abcUKGrowth" and the calculation is showing the difference between "abcUKGrowth" (1.18) and "lmn UKGrowth" (0.70) which is 0.48.
If you want to use the return instead of cost in the calculation just change the [Cost] to [Return] (considering you have them pivoted).
(IF [Fund Name Parameter]=[Fund Name]
THEN
[Cost]) //Here you could use return instead if you wanted to do ReturnCost//
END)

(IF [Fund Name Parameter]=[Fund Name]
THEN
{FIXED [Sector]: MIN([Cost]}
END)
Hope this makes sense.
If this helps you or answer your question, please mark it as helpful/correct so other users can refer to it. Thanks!
Best,
Diego

4. Re: Time series data calculation
Jeanne Sun May 22, 2019 5:45 AM (in response to Diego Parker)That's not what i mean. I want to calculate the difference in return for each period, between the selected fund and the lowest cost fund.
The formula you showed only calculate the different in cost between the selected fund and the lowest cost fund. If i swap cost with return in the formula, then it become the difference in return between the selected fund and lowest return fund.

5. Re: Time series data calculation
Diego Parker May 23, 2019 2:34 AM (in response to Jeanne Sun)Hey Jeanne,
I finally understood you (I believe!). Sorry for the delay in the answer and the misunderstanding.
The calculation goes as following:
IF ([Fund Name])=[Fund Name Parameter]
then
({ FIXED [Return Period],[Fund Name],[Sector]: SUM([Return])}) 
({ FIXED [Sector], [Return Period] : MIN( IF (if {FIXED [Sector]: MIN([Cost])} = [Cost] then [Fund Name] END) = [Fund Name] THEN [Return] END)})
END
Please find a workbook attached.
If this helps you or answers your question, please mark it as helpful/correct so other users can refer to it. Thanks!
Best,
Diego

Example.twbx 18.6 KB


6. Re: Time series data calculation
Jeanne Sun May 23, 2019 6:43 AM (in response to Diego Parker)This works perfectly! didn't realize it is so complicated. Thank you!