4 Replies Latest reply on Jun 14, 2013 7:58 AM by Jim Wahl

    How can I have dynamic reference bands?

    Erin Williams


      I have operational data with the following categories:  Outage Type, Region, Labor Involvement, Frame Size.  I have upper and lower bound info for each combination, so for example, a CI (Outage Type) in the US (Region) with an FE (Labor Involvement) on a 9F (Frame Size) has an UB and LB of X and Y.  On my dashboard I am using filters and radio buttons to show the different combinations of data and everything is working fine except for the reference band.  I can put it in using the parameter, but I end up with only the Asia, FE only, 7F, CI data, where I want it to change with each selection, as their are values for each selection.  Any help would be greatly appreciated.

        • 1. Re: How can I have dynamic reference bands?
          Jim Wahl

          Hi Erin,

           

          Maybe you've already tried this (and I'm missing some part of your problem), but why not create calculated fields for UB and LB. After you add these fields to the level of detail self they will be available in the reference line / band dialog box.

           

          If you have a lot of combinations, it might be easier to create and maintain an Excel file with the thresholds and use data blending to add this to your view.

           

          Jim

          • 2. Re: How can I have dynamic reference bands?
            Erin Williams

            Thanks for trying to help Jim.  So are you saying create a table to import as another data source with my 4 categories (Outage Type, Region, Labor Involvement, Frame Size), link it up with my original data set, and the UB and LB, and then use these as the parameters for the reference range band?

            • 3. Re: Re: How can I have dynamic reference bands?
              Jim Wahl

              It depends somewhat on your dashboard and data and how often these thresholds will change.

               

              If you're using parameters to filter, you could just concatenate these and use a case statement to lookup the threshold:

               

              I think something as simple as this might work for you

              Threshold_UB =

              CASE [Outage Type] + " | " + [Region] + " | " + [Labor] + " | " + [Frame]

              WHEN "CI | FE | US | 9F" THEN 100

              WHEN ....

              END

               

              Then you'll need to add Threshold_UB to the level of detail shelf with a ATTR() aggregation. And then the field will be available in the reference line dialog box. The ATTR() will return "*" (no reference line) if multiple values for a particular dimension are in the table, and, therefore, provides some nice error checking on the filter.

               

              OR, you can use a spreadsheet to enter and keep track of the values.

              You'd need columns for each dimension you want to filter on and columns for upper and lower thresholds. Once you create and add this as a data source in Tableau, you can drag the thresholds to the level of detail and the dimension fields will be automatically linked if those dimensions are in the view (and if they are not in thew view, you can manually link the fields by clicking on the chain-icon next to the fields in the secondary data source).

               

              I'm not sure if this is clear yet, but if you want to post some sample data, I could get you started. ...

               

              Jim

              • 4. Re: Re: How can I have dynamic reference bands?
                Jim Wahl

                I edited the above reply to use ATTR() for aggregation type for Threshold_UB, when you add this to the view.

                 

                Note that you can either use the parameter names or the dimension names in the CASE statement. In theory, it shouldn't make any difference, but the dimension names might be better, since ATTR() will fail if there are multiple values for a dimension in the view.