1 of 1 people found this helpful
Here's one way to do this, via Custom SQL using a UNION query. I took the original query generated by Tableau and duplicated it, adding two columns: One called Stage and Status that merges those dimensions, and a second called Type that just notes whether the data rows are coming from the Stage subquery or Status subquery.
Once you have this UNION query, then creating the crosstab is straightforward.
Note that if you do other calculations off this query, such as a SUM of the Total Measure, you will get 2x the expected results because the query is duplicating records. There are workarounds for this (like putting Type on the Filter shelf and just selecting one of the values), however I'd suggest using the original datasource for that portion of your analysis, and only use the UNION'd datasource when you need to .
Thanks for your response. i wanted to know if it is possible to implement it without a Custom SQL Union query? is it possibe to do it using a cal field?
Attached is a way using calculated fields. The difficulty here is that you have two dimensions, Stage and Status, and not only want to merge them but also count each record twice, once for each instance of the record for Stage and Status. What I did was create six calculated fields, for all the values of Stage and Status, and then used Measure Names as the dimension to bring them all in as Measure Values.
One way would be use 'Full Outer Join' between 2 dimensions and have a non matching condition and then you can create a calc in tableau to merge them into single column something this.
Result of Full Outer Join:
Status Dim 1 Stage Dim 1 Status Dim 2 Stage Dim 2
A Open NULL NULL
NULL NULL A Closed
Calcs would be :-
If Status Dim1 Is null
then Status Dim 2
create similar calcs for other columns too. I hope it helps.