1 Reply Latest reply on Aug 27, 2018 3:03 PM by Patrick A Van Der Hyde

    Rolling Correlation - LOD

    Jake Goldman

      Hi all,

       

      I am pretty new to Tableau and have been working on this for a few days. I've read through a ton of forum postings but haven't been able to work it out yet. Would sincerely appreciate any direction or assistance.

       

      I am trying to create a rolling 1 and 2 year correlation calc that will take into account security classification (4 options) and then calculate the correlation against the appropriate benchmark index. The worksheet I am trying to make this calculation in is a table with columns of various indexes, each with a unique ID and associated metric (Ex: SPX Index: ID = 1, Metric = Last Price, or SPX Index: ID = 2, Metric = Trail 12M EPS). It is worth noting that the only unique piece of each Index/ID/Metric combination is the ID. The rows are various calculations that have been performed on each combination of Index/ID/Metric. The data set contains many years of historic values, but the table cannot be filtered by date due to the other calculated rows (which are not present in the sample workbook). I have been using LOD calcs to produce most of the calculations so far.

       

      Here is what my formula looks like in excel, with each range in RSQ being the benchmark or index/metrics historical values:

       

      =IF(GK7="A",RSQ($ALS$76:$ALS$600, GK76:GK600),IF(GK7="B",RSQ($ALU$76:$ALU$600, GK76:GK600), IF(GK7="C",RSQ($ALV$76:$ALV$600, GK76:GK600), IF(GK7="D",RSQ($ALT$76:$ALT$600, GK76:GK600)))))

       

      Here is how I have been trying to replicate it in Tableau, except I want to replace three of the [SPXbenchmark] measures with different benchmark values:

       

      SPXBenchmark calculation:

       

      Without specifying security type or a date range, the following calc produces a table showing just a correlation of 1 for SPX's  with itself:

      Is there a way for me to create this rolling correlation calc that changes benchmarks depending on security type? And can this be accomplished within this table, without altering filters? I have considered trying to find the correlation in a different worksheet and then moving the data into this table, but to the best of my knowledge there isn't a Tableau function that would be equivalent to =cell reference in Excel.

       

      I am attaching a sample workbook that I have stripped of most data and all proprietary info. There were more calculated rows in the calculations tab but I removed them for this workbook. If there is anything I can clarify please let me know.

       

      Thank you very much for any help, I really appreciate it.

       

      Jake