1 Reply Latest reply on Jul 27, 2018 7:25 AM by Brian Sullivan

    Creating a Hierarchy of Franchises/Sites Using Parameter and Calculated Fields

    Brian Sullivan

      Hi,

       

      I am trying to create a dynamically updating hierarchy of sites within franchises that can be selected using filters and the parameter control (for franchise).  I have the dimensions Parent Org Name and Sites.  Parent Org Name lists the name of the organization that the site rolls up to.  I want to create it so that any sites with the same Parent Org Name will automatically be grouped within the same Franchise.  I would like this hierarchy to be "dynamically updating", so that if a site is added or removed from the data source, Tableau will automatically add/remove it from the Franchise grouping.  I had previously created this on an older data source that did not have Parent Org Names, so each site was hard coded in using a Case, and then a separate calculated field containing if statements that would then link it to the correct Franchise.  This measure was then added to the filter, selecting All, and then duplicated and added as another filter, excluding Null.  This allowed users to select a Franchise from the parameter control and have the site filter update with the corresponding sites belonging.

       

      Ex Site Case -> This measure was created for each possible Franchise within the parameter.

       

      CASE [Site]

           WHEN 'Site Name' THEN 'Franchise Name'

           WHEN ...

       

       

      Ex Calc Field w IF Statement -> This measure was duplicated and added as filters.

       

      IF [Franchise] = 'Franchise Name'

           THEN [Site Case x]

      ELSEIF 'Franchise Name'

           THEN [Site Case y]

      ELSEIF ...

       

      I am wondering how I can do this using the Parent Org Name so that my Sites will automatically reflect changes from its data source and no hard coding is required when a site is added/removed.

       

      Please see example data below.

       

      EDIT: I have been trying to accomplish this by creating a Parameter from Parent Org Name, and then adding the dimension "Parent Org Name" as a filter, and Add Context of the Franchises that I have available.  The problem with this method is that it is not filtering the sites based on the parameter control - it lists all available sites for any parameter when I need only the sites that roll up to the respective franchise.

       

      Ex Data

       

      Parent Org Names

      New York

      Pennsylvania

      Mass.

      NJ

      Ohio

       

       

      Site

      New York City

      Philly

      Boston

      Newark

      Cincinnati

       

      Message was edited by: Brian Sullivan