9 Replies Latest reply on Jan 17, 2018 12:28 AM by Simon Runc

    Blending Calculation Issue

    Minnu Meena

      Hi All,

       

      I am trying to have a calculated field using one both the primary and secondary data source.

      But whenever I'm aggregating the data based on the secondary data source, my calculated field returns error

      I have tried doing the calculation without aggregating but it seems to give error " all fields must be aggregate/constant while using from multiple data source".

       

      I created a calculation as "Select metric"

       

      case [Select Metric]

      when "Corporate Charges" then IF [Period] = "Actual" then ([Corp Charge]) ELSE null end

      when "Gross Margin" then IF [Period] = "Actual"  then ([Gross Margin]) ELSE null end

      when "Operating Margin" then IF [Period] = "Actual"  then ([Opr Margin]) ELSE null end

      when "Operating Profit" then IF [Period] = "Actual"  then ([Opr Profit]) ELSE null end

      when "Revenue" then IF [Period] = "Actual"  then ([Tot Rev]) ELSE null end

      when "Billable Hours" then IF [Period] = "Actual"  then ([Tot Bill Hr]) ELSE null end

      when "G&A" then IF [Period] = "Actual" then ([Tot Ga Exp]) ELSE null end

      when "GID" then IF [Period] = "Actual"  then ZN(SUM([JEG_TAB_GID_ALL+ (PROD_PA_BIDATA32) Extract].[Tot Mtd])) ELSE null end

      END

       

      This cal throwing error. Also find the attached workbook for your reference.

      Kindly please help me on this.

       

      Thanks,

      Minnu.

        • 1. Re: Blending Calculation Issue
          Vivek Nayagam

          Hi Minnu,

           

          Please try with this calculation:

           

          case [Select Metric]

          when "Corporate Charges" then IF attr([Period]) = "Actual" then sum([Corp Charge]) ELSE null end

          when "Gross Margin" then IF attr([Period]) = "Actual"  then sum([Gross Margin]) ELSE null end

          when "Operating Margin" then IF attr([Period]) = "Actual"  then sum([Opr Margin]) ELSE null end

          when "Operating Profit" then IF attr([Period]) = "Actual"  then sum([Opr Profit]) ELSE null end

          when "Revenue" then IF attr([Period]) = "Actual"  then sum([Tot Rev]) ELSE null end

          when "Billable Hours" then IF attr([Period]) = "Actual"  then  sum([Tot Bill Hr]) ELSE null end

          when "G&A" then IF attr([Period]) = "Actual" then sum([Tot Ga Exp]) ELSE null end

          when "GID" then IF attr([Period]) = "Actual"  then

          sum([JEG_TAB_GID_ALL+ (PROD_PA_BIDATA32) Extract].[Tot Mtd]) ELSE null end

          END

           

           

          Refer this URL for more details:

          Error “All fields must be aggregate or constant…” | Tableau Software

           

          Also please check the blend linking fields.

           

          Thanks,

          Vivek

          • 2. Re: Blending Calculation Issue
            Michal Mokwinski

            Hi Minnu,

             

            the issue here is that blends aggregate the data from secondary data source, in your case to the level of PU.

            Because JEG_TAB_GID_ALL+ (PROD_PA_BIDATA32) Extract].[Tot Mtd] field is already an aggregation (on the blend level) you need to aggregate other measures - you cannot operate on row level like you would like to.

             

            The simplest solution is the following:

             

            case [Select Metric]

            when "Corporate Charges" then IF attr([Period]) = "Actual" then sum([Corp Charge]) ELSE null end

            when "Gross Margin" then IF attr([Period]) = "Actual"  then sum([Gross Margin]) ELSE null end

            when "Operating Margin" then IF attr([Period]) = "Actual"  then sum([Opr Margin]) ELSE null end

            when "Operating Profit" then IF attr([Period]) = "Actual"  then sum([Opr Profit]) ELSE null end

            when "Revenue" then IF attr([Period]) = "Actual"  then sum([Tot Rev]) ELSE null end

            when "Billable Hours" then IF attr([Period]) = "Actual"  then sum([Tot Bill Hr]) ELSE null end

            when "G&A" then IF attr([Period]) = "Actual" then sum([Tot Ga Exp]) ELSE null end

            when "GID" then IF attr([Period]) = "Actual"  then sum([JEG_TAB_GID_ALL+ (PROD_PA_BIDATA32) Extract].[Tot Mtd]) ELSE null end

            END

             

            But it's not the best. Because of the ATTR(Period) you have to use the Period in your chart or filter out values other than Period = "Actual" (as in the calculation). Otherwise the ATTR() will return aggregation error (*).

             

            If you would give more context where and how you would like to use the calculation I might give you better answer.

             

            MichaƂ

             

            EDIT: wasn't fast enough

            • 3. Re: Blending Calculation Issue
              Minnu Meena

              Hi Michal,

               

              Thanks for the quick reply. I am creating a parameter to show all the metrics performance together in one view. so i am getting another one metric from different data source that i need to show it in the same parameter.

              Here i am facing the issue. How can i solve this?

              • 4. Re: Blending Calculation Issue
                Michal Mokwinski

                This I understand from the calculation, but will you be using the value standalone or create a table or chart with the value?

                • 5. Re: Blending Calculation Issue
                  Minnu Meena

                  Using the value as standalone. That parameter should apply in dashboard globally.

                  • 6. Re: Blending Calculation Issue
                    Michal Mokwinski

                    So the formula I wrote before should be fine, just use the filters.

                    • 7. Re: Blending Calculation Issue
                      Minnu Meena

                      Yeah I tried that formula but it is not working, throwing some error.

                      • 8. Re: Blending Calculation Issue
                        Minnu Meena

                        Hi Simon Runc can you please help me on this issue?

                         

                        Thanks,

                        Minnu.

                        • 9. Re: Blending Calculation Issue
                          Simon Runc

                          Hi Minnu,

                           

                          I’m on holiday until the end of January, so no access to Tableau. I’ll take a look when I get back if you haven’t found/got an answer by then.

                           

                          As has been mentioned, all fields from secondary sources come in as aggregates, so your options are to try & join/union them into a single source, or have the VizLoD such that an aggration works (only taken a quick look but you’ll likely need [Period] in your VizLoD)