6 Replies Latest reply on May 23, 2019 6:43 AM by Jeanne Sun

# Time series data calculation

Hi guys,

Appreciate any help.

I have some time series data (sample below). I use Fund Name as parameter, and would like to calculate the difference in return for each period between the selected fund and the lowest cost fund within the same sector. In Tableau, I have pivot the returns and time periods.

For example, if I select "abc UK growth", the difference in return for each period would be between "abc UK growth" and "lmn UK growth", because it has the lowest cost (0.70) in the "UK Small cap" sector.

I'm thinking about using Fixed LoD, but not sure how to write the code. However if you have any other suggestion, please let me know as well.

Fund nameSectorCostReturn for period 1Return for period 2Return for period 3Return for period 4Return for period 5Return for period 6Return for period 7Return for period 8
abc UK growthUK small cap1.185.204.856.026.245.335.095.957.89
abc US valueUS small cap1.285.465.465.756.355.065.025.376.44
efg UK growthUK small cap0.849.069.3410.7310.217.919.228.078.94
efg US valueUS small cap0.775.204.806.336.795.965.856.896.96
lmn  UK growthUK small cap0.705.265.716.546.274.684.515.115.93
lmn  US valueUS small cap0.805.555.357.347.356.156.427.008.07
xyz UK growthUK small cap1.007.157.257.997.536.145.686.115.83
xyz US valueUS small cap1.633.574.195.185.433.504.404.876.03

Thank you.

• ###### 1. Re: Time series data calculation

Hi Jeanne,

The formula is:

(IF [Fund Name Parameter]=[Fund Name]

THEN

[Cost]

END)

-

(IF [Fund Name Parameter]=[Fund Name]

THEN

{FIXED  [Sector]: MIN([Cost])}

END)

Best,

Diego

• ###### 2. Re: Time series data calculation

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

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 Return-Cost//

END)

-

(IF [Fund Name Parameter]=[Fund Name]

THEN

{FIXED  [Sector]: MIN([Cost]}

END)

Hope this makes sense.

Best,

Diego

• ###### 4. Re: Time series data calculation

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

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