5 Replies Latest reply on May 4, 2015 12:11 AM by Chris Gadd

    Dynamic fields on chart

    Chris Gadd

      Hi,

      I've created a chart with dynamic fields based on this tutorial. This works great, however I'm now trying to expand this and running into problems with aggregation of a secondary data source. Following that example, I start with a calculated field like this:

      CASE [Parameter1]

      WHEN "Sales" THEN [Sales]

      WHEN "Volume" THEN [Volume]

      WHEN "Market share" THEN [Market share]

      END

       

      So that works fine. But I have some additional fields in a secondary data source (Excel sheet) which I'd like to look up and include in the parameter. This Excel file is just a list of unique IDs (joined to my primary source) and additional detail on that ID. Picture a customer database and you have the idea. My problem is I get the "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources." error when using the secondary data source.

      Any ideas on how to get around that? I tried using ATTR but I then have to apply that to all fields in the calculated field and my charts no longer show a line for each measure, just one line labelled '*'.

       

      For clarification, what I'm ultimately trying to achieve is a chart plotting a single time series measure, but where the list of elements plotted is according to some attribute that I dynamically choose (eg region, ID, manufacturer).

      thanks,

      chris

        • 1. Re: Dynamic fields on chart
          Sankarmagesh Rajan

          hi Chris

           

          when we blend two data source and use parameters means we can use same type of aggregation in both fields.

           

          like here. i have sample super store and coffee chain dbs.

          I want to take sales from coffee and remaining from super store excel.

          When we take sales from coffee chain it come as

          "SUM([CoffeeChain Query (Sample - Coffee Chain)].[Sales])"

          when we take profit from super store then it comes as

          [Profit], it missed sum here so we can use both with sum[]

           

          case [Measure Parameter]

          when "Sales" then SUM([CoffeeChain Query (Sample - Coffee Chain)].[Sales])

          when "Profit" then sum([Profit])

          end

           

          When we use dimensions

           

          case [Dim Parameter]

          when "Customer Segment" then attr([Customer Segment])

          when "state" then ATTR([CoffeeChain Query (Sample - Coffee Chain)].[State])

          end

           

          both should be attr[]

           

          Please attach sample workbook.

          Thanks

          sankar

          • 2. Re: Dynamic fields on chart
            Chris Gadd

            Thanks @sankar.rajan, appreciate the help. It's your second example of using a dimension I'm trying to achieve. I've attached a sample workbook with two sheets. The first uses a calculated field with no aggregation - Chart drilldown (primary source only):

            CASE [Chart Parameter]

            WHEN "Continent" THEN [Continent]

            WHEN "Department" THEN [Department]

            END

            This works fine to display sales by Continent and Department.

             

            The second sheet attempts to extend this to display other attributes of a continent from a secondary source, which requires aggregation of all dimensions, see the calculated field Chart drilldown (aggregation):

            CASE [Chart Parameter]

            WHEN "Continent" THEN ATTR([Continent])

            WHEN "Department" THEN ATTR([Department])

            WHEN "Drought" THEN ATTR([Sheet1 (continent info)].[Drought])

            WHEN "OECD ranking" THEN ATTR([Sheet1 (continent info)].[OECD ranking])

            END

             

            This plots all charts as a single line, ie aggregation of all continents or departments.

            Any ideas?

            • 3. Re: Dynamic fields on chart
              Sankarmagesh Rajan

              hi Chris,

               

              When we do this calc in separate then it works fine.

              when we blend two calc then it will show in measure place and it will not moved from here to dim.

              Hi

              Mark Fraser  please check this.

               

              Thanks

              sankar

              1 of 1 people found this helpful
              • 4. Re: Dynamic fields on chart
                Chris Gadd

                Thanks Sankar. Will see what @mark.fraser.0 has to say but I think you're suggesting the problem is that I've ended up with a measure rather than a dimension. And I understand that's because the calculation includes an aggregation, which I require because of the secondary data source.

                 

                I've no doubt there are other ways to achieve this, such as combining the data at the source, or a worksheet for each chart view I want and some dashboard parameter to dynamically choose the sheet. I'm hoping for something fairly simple though!

                 

                Also, please let me know if there's any feature in a more recent version of Tableau (I'm using 8.2) that would help.

                 

                thanks again,

                chris

                • 5. Re: Dynamic fields on chart
                  Chris Gadd

                  Just a followup for others, I tried v9.0 and the same problem. I think rather than dynamically choosing a different sheet I'll try to put the secondary data into the primary source. Fiddly and annoying at that end, but the Tableau work will be simpler.