7 Replies Latest reply on Feb 22, 2017 12:54 PM by Yingying Zeng

    Cascading Parameter? no dynamic domain population?

    Yingying Zeng

      Jamieson Christian

       

      Hello Jamiseson,

       

      Your full explanation on my past question in the below link helps me a lot to further understand data blending common filter. Thanks a million.

      data blending -Does the linked field interact with each other?

       

      Based on your explanation there, I have some extra question and want to ask your insight to get more knowledge

       

      In your original question, you mentioned "

      Due to the caveats of using blended data for filtering, Parameters are an appealing alternative to Quick Filters. Parameters span data sources, and so are fairly robust for filtering. The typical disadvantages of Parameters apply — no dynamic domain population, no discrete multi-select, etc. — so make sure that your desired user experience is tolerant of these disadvantages before leveraging Parameters."

       

      I have a user case here and want to make sure whether the no dynamic domain population you mention here means the no cascading parameter or it means different thing.

       

      In the attached twbx file , the first dashboard we have here called One parameter control two data source_state. This dashboard use the same parameter State to apply to internal and external data to make the comparison. We populated  the state to the details level in the parameter. The detail parameter( such as MD) is  applying to both internal and external.

       

      The second dashboard, which called one parameter control two data source_cbsa,  did the same thing,populate the CBSA to the same parameter and the detailed cbsa apply to both internal and external data by one parameter.

       

      However, The above two dashboards themselves is great while when

      1.  we want to combine cbsa and state in the same dashboard

      2.  use the populated cbsa details or state details to drive both internal data and external.

       

      I got stuck since my parameter only go to first level- combine the cbsa and state and I do not know can not go further to get the level 2 populate  the cbsa or state and apply the details to both sheets.  What I have now is the dashboard 3, first level parameter CBSA and State flip apply to both internal and external sheet but the second level cbsa, state details need to use separate filters for internal and external sheet.

      Is that is any way to create a cascading parameter  to populate the State (MD etc) and CBSA into the level 2 parameter and let the level 2 parameter works on both sheet so that details state or CBSA can control both sheets?

       

      Hope that my explanation of the case is not that bad but let me know if there is anything which is unclear

        • 1. Re: Cascading Parameter? no dynamic domain population?
          Yingying Zeng

          The Vizioneer: Clickable Drillable Cascading Dynamic Parameters in Tableau

           

          Want to save the link here to further discuss about cascading dynamic parameter after I get more insight about cascading parameter

          • 2. Re: Cascading Parameter? no dynamic domain population?
            Jamieson Christian

            Yingying,

             

            I have a user case here and want to make sure whether the no dynamic domain population you mention here means the no cascading parameter or it means different thing.

            That is correct. Because parameter options are not populated based on the data, they cannot be used for cascading filters.

             

            I looked at your worksheet, and you are using Tableau 10.0.2. This means you can leverage cross-datasource filters, per my response in Re: data blending -Does the linked field interact with each other?  (the section "As Of Tableau 10"). Using cross-datasource filters will allow you to avoid data blending to make your filters work, and also avoid the disadvantages of parameters.

            1 of 1 people found this helpful
            • 3. Re: Cascading Parameter? no dynamic domain population?
              Yingying Zeng

              Hello Jamison, Thanks a lot for your reply .

              Yes I am using 10.1 and I am using cross data source filter.

              However, here is an issue with different data structure for internal and  external data.

              Internal data all the geographic level (CBSA, State...) are in one level or external data is union cbsa and state, when some cbsa in internal data is not in external and  I am missing some data. I think  the reason is that we are  using one of the filter either internal or external as the leading filter. To avoid the data missing I need a overall filter /parameter to control both filters ( internal and external) to get the data back. which is the reason I try to figure out what parameter can do. 

               

              Let me review some data and probably have an extra question  for you before closing the question here 

              Thanks a million.

              • 4. Re: Cascading Parameter? no dynamic domain population?
                Jamieson Christian

                Yingying,

                 

                I took a closer look at your data. If you're essentially trying to get a state (or state group) out of each row, in order to join them, then try the following calculated field on both data sources:

                 

                [Extracted State]

                IF [Cbsa] = 'NULL' THEN [State]
                ELSE MID([Cbsa],FIND([Cbsa],",")+2,999)
                END
                

                 

                This calculated field yields the following in each data source:

                 

                 

                Does that give you what you need to effect a proper filter across both data sources?

                1 of 1 people found this helpful
                • 5. Re: Cascading Parameter? no dynamic domain population?
                  Yingying Zeng

                  Jamieson Christian

                  Thanks so much for your reply , it did help me to understand something I did not realized before.

                   

                  However, below is a raw summary about the data structure and requirement.

                  We want to compare internal sales and external sales in selected geographic area with the below data structure

                   

                  1. Internal data : CBSA/STATE can align to row level for sales.

                  2.External data

                  a.the data set we get externally (industry sales numbers) from CBSA/State is separate so that they can not align in the row level but either put in two tables or union them to keep it independent.

                  b.Since CBSA/State is not correlated, when users choose CBSA, the state should show null while users choose state, the cbsa is statewide. ( which is the option 1 in the twbx book)

                  c. For certain CBSA, for example, any cbsa related to Boston, no data can be found,no row in external  has related cbsa.

                   

                  Based on the requirement on external data and its data structure.

                   

                  I have the option 1 in the attached twbx book but when we choose,  any data related to Boston cbsa, the  internal data is gone. ( I am not so clear why it is gone, I guess it is related to how cbsa, state, internal/external data link)

                   

                  Missing the data is not a good thing while there is actually underlying data so I create the option2 to let the cbsa and state has its own filter ,which is the reason I am wondering whether we can create a cascading parameter (level one, choose CBSA/State parameter, Level 2 : choose detailed cbsa/state in the parameter) to control both cbsa filter and state filter internal and externally.

                   

                  I hope my explanation  do not confuse you more while if you have time to have a look at this weird situation.

                   

                  I want to have your thought about why the internal data is gone for the Boston related cbsa area for option 1 and for option 2, we can not create cascading parameter so that we have to  use separate level 2 geographic filter in internal and external data right( see option 2 dashboard for your reference)

                  • 6. Re: Cascading Parameter? no dynamic domain population?
                    Jamieson Christian

                    Ying,

                     

                    I must confess, I still don't have my head wrapped around exactly what you want the final user experience to be. Maybe it would be easier to start by defining exactly what your ideal experience looks like (in terms of very specific use cases, i.e. "user selects filter X, the cascading filters then show Y, and the data to include in the view is Z").

                     

                    Here are my deductions and assumptions about your requirements, based on what we've discussed so far. I suspect that I have some of these wrong, so please help clarify where the requirements are in error.

                     

                    1. User should be able to select that they want to view the data either at the CBSA level or the State level.
                    2. If they choose CBSA, the next drop-down should show a list of CBSA for them to filter by. Likewise, if they choose State, that drop-down should instead show a list of States to filter by.
                    3. If they filter by CBSA, the data should include everything that is explicitly associated to that CBSA, and (???) the State is not taken into account at all.
                    4. If they filter by State, the data should include everything that is explicitly associated to that State, and (???) the CBSA is not taken into account at all.

                     

                    Assuming the above requirements are accurate, here is my assessment:

                     

                    #1 and #2 are easy. The selector for "CBSA" or "State" will be a parameter. A calculated field will populate with either the value of [CBSA] or [State], depending on how the parameter has been set. This calculated field will serve as a Quick Filter to act as your second drop-down.

                     

                    #3 and #4 are where we need to very succinctly define exactly how to segment our data in our calculated Quick Filter.

                     

                    CBSA

                     

                    • EXTERNAL — Assuming that CBSA data and "Statewide" data are considered mutually exclusive (you only need to report one or the other), then the calculated Quick Filter will be enough to properly segment the External data.
                    • INTERNAL — It seems that each State has a number of CBSA's as well as an entry with a "null" CBSA. If the "nulls" can be safely ignored when filtering by CBSA, then the calculated Quick Filter will be enough to properly segment the Internal data.

                     

                    STATE

                     

                    • EXTERNAL — It appears that [State] is null except for "Statewide" rows. If these rows are the only ones that should be taken into account when filtering on State, then the calculated Quick Filter will be enough to properly segment the External data.
                    • INTERNAL — When filtering on State, should we explicitly exclude rows that have a CBSA and focus only on the rows for which CBSA is null? Or include all rows for which the State matches? The answer will change the calculated field a bit, but either way, as a Quick Filter the calculated field should still be enough to properly segment the Internal data.

                     

                    The bottom line is that you need an appropriate field upon which to blend your data, or you will get (as you've seen) an empty result for one of the other of your data sources. The field you use to blend your data should be the same field that is used for the Quick Filter described above.

                     

                    Based on how I've interpreted the requirements (and the myriad assumptions I've made above), I would start with the following calculations for Internal and External, respectively. These calculations assume a parameter named [Filter By] that can be either "CBSA" or "State". You will use these two calculations to blend your data sources, and you will use one of them to also act as your cross-datasource Quick Filter (to appear alongside the [Filter By] parameter).

                     

                    [External Data Blend]

                    CASE [Filter By]
                    WHEN 'CBSA' THEN [cbsa]
                    WHEN 'State' THEN [state] // Assumes that State will be null unless [cbsa] is "Statewide"
                    END
                    

                     

                    [Internal Data Blend]

                    CASE [Filter By]
                    WHEN 'CBSA' THEN [CBSA]
                    WHEN 'State' THEN
                         // (Assumes that only the "null CBSA" rows should be included)
                         IF ISNULL([CBSA]) THEN [State] END
                    END
                    

                     

                     

                    I hope this helps.

                    1 of 1 people found this helpful
                    • 7. Re: Cascading Parameter? no dynamic domain population?
                      Yingying Zeng

                      Jamieson Christian

                       

                      Thanks a lot for the reply

                      The requirement is very confusing and actually it went  deeper on whether we have find the data from the market or not and which level of the granularity what we have from the data etc.

                       

                      Let me give a try for your approach to see what we got