Replicating Excel CORREL functions in Tableau and better leveraging LOOKUP in Tableau
colum.mccoole Jul 25, 2013 6:36 AMI've been trying to see how much functionality which I'm used to doing within excel is portable across to Tableau, so that I can bring a whole solution within Tableau.
The structure of the data is very basic (as per the DataRaw) tab in the attached excel ... a date, Type (Series or Benchmark), Field (description) and Value. I'm not sure whether my approach is the correct one or not, but I've used various calculated fields to split this Value into either Benchmark or Series and then align both timeseries (in this basic example) in order to generate various correlation calculations.
While I can get the full series correlation working (yellow cell in screenshot) ... anything involving a subset of that timeframe is not working (where I am trying to control the 'WINDOW_*' using start and end optional settings.
I have been trying to follow best practices in terms of going into the edit table calculation dialogue 'advance' and setting calculations to run along WeekEnding. In my interim calculations to get WoW absolute and percent changes, I used ascending sort against WeekEnding in order to get the right calculated data aligning ... complicated partly by the fact that I am showing the table here ordered by newest to oldest.
When editing a table calc for the correlation calculation against setting it to calculate against 'WeekEnding', it doesn't seem to matter the sort order in the 'advanced' tab ... for the main calculated field or component ones. Is this normal?
The above is a basic example of a single correlation analysis. In the attached *.twbx (and screenshot below), on the continued theme of moving full functionality from excel, I'm also trying to see the best approach to building out a heatmap that takes the latest correlation data for lots of Benchmark/Series pairs and perhaps augments this with some data on max/min values and dates which would be generated within Tableau (rather than imported as meta data from outside). I have read on the forums in terms of certain limitations on generating a rank on data output from Table Calculations ... but unclear as to whether there is a workaround for this.
Finally .. if anyone has pointers on the regression/correlation panel / slope graph ideas I elaborate on inside this twbx, that would be ideal.
Thanks,
Colum

correl_question.twbx.zip 540.0 KB

tableau_correl_question_data.xlsx 817.4 KB