Since you have your data in 2 tables it is rather complicated to replicate excel's vlookup results. Anyway this is based on cross joined data:
I would suggest reshaping data first.
planner cross join.twbx 29.2 KB
Many thanks for your help. The end result looks great, but I'm struggling a little to understand the logic behind how you achieved this.
You added a blank column named 'x' to data ranges FC and FD, and then joined based on this column. Does this simply allow the data to be joined despite not having any common fields?
Range FC is an aggregate of data from range D. I constructed this in Excel as I didn't know any other way to get my end result, but it's aggregate data, not raw data. In my real data set I have other data that would be useful to analyse such as suppliers, stock levels etc that gets lost in the aggregation step. Can I get straight to the end result without using the intermediate table FC in Excel, or does this need to be generated outside of Tableau?
Lastly, I'm struggling to wrap my head around the table calc 'Calculation 1' that you created. I don't quite get the indexing logic that you built. Is there any way to provide an overview of the logic here?
Thanks so much for your help, and sorry for all the additional questions!
1 of 1 people found this helpful
Since tableau does not allow cross joins to be made in its join interface I used that dummy field 'x' (in fact I was surprised it worked with blanks/NULLS as you cannot compare those in SQL so some value should be used instead).
You may get straight results by creating a cross joined table in excel - is excel your actual db for this?
Indexing logic is complicated....
I think your goal is to have a diagonal matrix with month on X & Y so the real data lies only on that diagonal line (1x1, 2x2, ...) whereas you want to display a multiplied values based on relative position against X & Y so I had to trigger 'data densification' (or 'domain completion' - I never know what's the right name) and lookup the values from the diagonal line.
Thanks for the detailed explanation. My real data is in excel, so I'll try the cross-join method that you mention above. It may be an option for me to restructure the original data, but that will take some time so I need to carefully consider options.
On the indexing logic, I think I'll need to spend some time experimenting to try and wrap my head around it fully!