I have a sales table and goals table that are linked indirectly. Due to the complexity of the data model, it was necessary make a territory table for both sales and goals. Both territory tables contain two terriroty types Team and State (States Rolling up to Teams).These tables are not flat....meaning that the table will contain two rows for each record (one for each terriroty type). I want to create a pivot chart that contains both my sales numbers and corresponding goals by Team and when expanded by State. Assuming I use Sales Terriroty as my demension, is there a way to use set analysis to link Sales Territory to Goal territory and show the corresponding goal? The code I am using is below....I have yet to have any luck.
Set analysis will not consider the actual dimension values, the set expression is evaluated once for the chart.
Try using a simple condition here:
sum( if( Goal_TerritoryGroup_Name=Sales_TerritoryGroup_Name, Goal) )
But I must admit, I haven't fully understood your setting, maybe a table view would help.