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

    Time series data calculation

    Jeanne Sun

      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
          Diego Parker

          Hi Jeanne,

           

          Please find the solution attached.

           

          a1.PNG

           

          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

          • 2. Re: Time series data calculation
            Jeanne Sun

            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

              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.

               

              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

                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

                  Hey Jeanne,

                   

                  I finally understood you (I believe!). Sorry for the delay in the answer and the misunderstanding.

                   

                  a1.PNG

                   

                  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

                  1 of 1 people found this helpful
                  • 6. Re: Time series data calculation
                    Jeanne Sun

                    This works perfectly! didn't realize it is so complicated. Thank you!