This hasn't been clear, and I'm still not sure I'm clear on what you need...
Are you saying if the user selects "Delta" from the Terrirories parameter, then the other sheets would display LA, MO, MS, TN, AR ?
Yes, you are right!!
Also an additional requirement. in test 5 version attached in my earlier comment. if you change policy state parameter e.g default state filter is "IA" and then change to "NE" on Dashboard 1. The metrics "Number of Claims" and "Indemnity" under "Total Stats from MP Policy" do not change at all. I want Number of claims and indemnity to be for only selected state.
However Indemnity and Distinct count of claim Id change on sheet "Total Stats from MP Claims".
Let's do one thing at a time.
You can get Sheet1 data source to talk to the other two so that if someone selects a [New territory] (which impacts Sheet1 data source) you can let Policy Data know which states to deal with.
In Sheet1 data source I created a calc: [in new terr?] it looks like this:
sum( if [New Territory] = [Parameters].[New Territory] then 1 else 0 end)
What that does is set a 1 or 0 to indicate which state(s) are in the new Territory.
I created a custom relationship between Policy Data and Sheet1 data sources to hook up Policy State from one with State Name with the other. (Click Data pulldown, select "Edit Relationships", select the two data sources you need to hook, and choose CUSTOM to tell it which field from each to use. So select Primary Data Source = Policy data, and click on Sheet1 for secondary, and click the CUSTOM button. Then click ADD...)
In Policy Data I created a calc: [Calculation1] it looks like this:
if ([Sheet1 (State_County_Territory Mapping)].[in new terr?]) >0 then 1 else 0 end
Now states in Policy Data will know if they are in the selected New Territory. I put that calc on filters and selected for value = 1.
Now look at Sheet 4 in the uploaded example. When you select a New Territory (which has multiple states in it), Sheet4 only grabs from Policy Data only those states that have New Territory value in Sheet1 data source.
So this does what I described in my question on Friday. If you can create the same setup for MP Claims data source, then you know you can make this work in your actual workbook.
I'm looking at your follow-up questions.
You haven't specified a blend field between Policy Data and MP Claim Data.
When you are on the Total Staff sheet, click on MP Claims Data datasource. You'll see that many fields have grey links with a slash through them. If you click on one (I clicked on [Policy State] it will turn orange. That is the indication that you have a blend field in effect. (You'll probably recall that Tableau gave you an errir saying fields cannot be used from MP Claims when you dragged AFF(Indemnity) to the measures shelf, for example. That was tableau's way of telling you that you didn't have a blend set up.)
My guess is that in your actual data you'll probably want to use PolicyID for this, but in the current example data set it has been deleted.
When you have this link, then for each occurrence of a value in the primary data source, you get aggregations of measure values from the secondaty data source.
There are a ton of white papers and explanations of data blending out there. Google around to get a broad discussion of the techniques.
Here's one you can start with:
To your first response (timestamp: Jun 29, 2015 1:43 PM)
In the attached version (test 6), when i am selecting delta on sheet 4 i am not getting any territories however it should return LA, MO, MS, TN, AR.
Basically output returned on sheet 4 and sheet 3 should be same.
Am i missing something?
I had to choose OK/TX because none of the states from Delta is in Policy Data, as far as I can tell.
Regarding Data blending seems like i lost blend setting on Reinsurance year dimension in long iteration of our conversation. I already went through topics around blending and global filters but this problems seems new to me.
PFA. I have blending done on Reinsurance Year dimension as the data in this view (Total Stats from MP Policy) is rolled up on Reinsurance Year.
So if you play around with Policy state parameter e.g. at for state IL 2014 Indemnity and #claims equals $24,141 and 3 and 2015 Indemnity and #claims equals $3,021 and 1
However thats not happening at the moment
Also seems like "All" option in New Territory Parameter control does not return all states from policy data snapshot data source in the current applied logic.
When you join on the year, tableau makes no distinction about which state from the primary data source matches with which state from the secondary. Even though the filter on the primary gets just state="IL", when it pulls records and sums them up from the secondary data source, it just gets all records for 2014 because that's all the blend was told to do.
See attached. I added [Policy State] as an additional blend field in the secondary data source. Now tableau knows to match up not only 2014 with 2014, but also IL with IL.