4 Replies Latest reply on Sep 6, 2016 5:47 PM by Jonathan Drummey

    Unsupported Aggregation, Quick Table Calc Sets, and Filters from Secondary Data blends?

    Christopher Amherst

      I've been hitting the following error in our Tableau 9.2 workbooks: "cannot blend the secondary data source because one or more fields use an unsupported aggregation"


      This has been occurring on worksheets where we implemented the sorting workaround for percent difference calculation
      (See Sorting Concepts Related to Quick Table Calculations | Tableau Software )

       

       

      For those worksheets, we use a parent field (Fiscal Year) as a sortable field and a set that duplicates Fiscal Year for the percent difference calculation.
      (Main reason for that decision was to allow for our users to sort the fiscal year in ascending or descending order but not affect the percent difference calculation between years)

       

       

      When attempting to add a field as a filter from our secondary data source, we get the above error

       

      Suggestions on a possible solution?  Is there a better way to retain the percent difference calculation without using sets in 9.2?

        • 1. Re: Unsupported Aggregation, Quick Table Calc Sets, and Filters from Secondary Data blends?
          Yuriy Fal

          Hi Christofer,

           

          The error you've got is triggered by a blend --

          it happens if any calculated field on a view

          is using COUNTD() aggregated function --

          or a LOD expression -- as a part of a calculation itself, 

          or if it is referencing such a calculated field.

           

          Please check your calcs on a view.

           

          Yours,

          Yuri

          • 2. Re: Unsupported Aggregation, Quick Table Calc Sets, and Filters from Secondary Data blends?
            Jonathan Drummey

            Hey Yuri,

             

            As an FYI there are situations where COUNTD(), MEDIAN(), and PERCENTILE() (the non-additive aggregations) and LODs can work in Tableau data blends.

             

            Non-additive aggregates are generally not usable when a secondary dimension appears on any Shelf or there’s a linking dimension that is not in the view. These latter two situations trigger a different style of data blending (DB2) inside Tableau to ensure an accurate blend and that makes computation of non-additive aggregates much more difficult.

             

            I’m less confident about LOD expressions because I haven’t spent as much time with them in data blends, I’ve had luck using FIXED LODs (such as using a FIXED LOD to return a first date per customer and then using that to create a days difference measure), not so much with INCLUDE & EXCLUDE LODs. My guess has been that it’s also due to DB2 blending. One definite no-go with LOD expressions is that they can’t use fields from multiple data sources inside a single LOD expression: i.e. {FIXED  : SUM(.[Sales])} is not supported.

             

            In any case resolving this particular error situation can be a real pain. Here’s what I first do:

             

            1) Are there any secondary dimensions being used on the Filters Shelf or other Shelves that could come from the primary instead? I like to have my primary and secondary dimensions have the same names whenever possible and that sometimes leads to me accidentally bringing the secondary dimension instead of the primary into the view and that’ll trigger DB2 blending and break calcs.

             

            2) Are there any secondary dimensions being used on the Filters Shelf or other Shelves that could be aggregated using ATTR, MIN, MAX, or AVG? Again, it’s the presence of secondary dimensions that triggers DB2 blending so if we turn the dimensions into aggregates then we won’t get DB2 blending.

             

            For #1 and #2 if you are using data blending to get labels or higher-level categories then it’s worth looking into Tableau’s Create Primary Groups functionality or using Tableau v10’s new ability to join across data sources.

             

            3) Are there any linking dimensions that don’t need to be used?

             

            4) Is it possible to add dimensions from the primary to the view such that all linking dimensions are primary dimensions and in the view?

             

            5) If adding all the dimensions from #4 will “break” calculations or the view (such as by increasing the number of marks) is it possible to use table calculations or other aggregations to return the desired results? For example I might need to add a dimension to the view to get my non-additive aggregate or LOD to work and then I get a lot of extra marks, then I could use something like IF FIRST()=0 THEN SUM() END to get back to a single mark for each partition. This can get really complicated really quickly and is not always feasible given knowledge levels, performance constraints, need for grand totals, filter actions, etc. There are situations like this where I’ve gone back to the data source to build out SQL views that did pre-aggregation, used Alteryx to join in additional information so I didn’t need a data blend, etc.

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: Unsupported Aggregation, Quick Table Calc Sets, and Filters from Secondary Data blends?
              Yuriy Fal

              Hi Jonathan,

               

              Thank you for the help.

              Frankly, I've got a hope for your reply --

              to get another bookmark of yours :-)

               

              This is the sentence of Christopher that triggered my initial reply:

              When attempting to add a field as a filter from our secondary data source, we get the above error

               

              Yours,

              Yuri