3 Replies Latest reply on Apr 25, 2016 6:11 AM by Carl Slifer

    Sum() the Attr() in calculated field from two data sources

    Marcus NG

      I'm the beginner of Tableau, I would like to do the Service Level Report which involves different database and dimensions. So, multiple data sources must be required. I encountered the common Tableau issue "Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated." which is sum() the aggregated calculated field [use Attr() to compare two data sources fields]. Any experts can provide the work around for the above issue?

       

      I prepared the sample of my case.

        • 1. Re: Sum() the Attr() in calculated field from two data sources
          Carl Slifer

          Hi Marcus,

           

          There are several issues with your calculations.  I do not think we have a solid understanding of ATTR() so lets start there.

          Attribute checks if the MIN of a field is the same as the MAX of a field. If it is then it will return that Field. If not it will return an asterisk. You have multiple scores for each person so you get an asterisk for each person. This equates to a NULL when used in a calculated field and you're unable to get any values for the 'No achieved target' and by result the 'hit rate'.

           

          Ok so with that established lets look at the 'No Achieved Target' Field

          if ATTR([Score]) > ATTR([Person Only].[Target Standard]) then 1 else 0 end

          I will change the ATTR(Score) to MAX in this case. if MAX([Score]) > ATTR([Person Only].[Target Standard]) then 1 else 0 end

          It will look through all of your scores and choose the maximum value and then compare that to the target standard for the person only database. This results in only a 0 or a 1. This is not going through each score like you imagine. When you blend you are comparing already aggregated values.

           

          Finally Hit Rate is using

          an aggregate divided by a row level field (non-aggregate)

          If you instead use [No. of Achieve Taraget]/SUM([Number of Records])

          You will be aggregating the number of records field at the same time and the calculation is valid, but probably not accurate for what you want.

           

          It looks like you want to compare every instance of your score to the target standard in the other database. Find out how many were greater and then divide that by the total number of scores. With blending I do not think this is possible. Again when you blend you take the aggregate of both sides and then compare in your case you are trying to aggregate the comparison and it doesnt work that way.

           

          Where are these datasources stored if they are in the same database you could do this with a join instead.

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: Sum() the Attr() in calculated field from two data sources
            Marcus NG

            Hi Carl,

            Thanks for your explanation. You are right, I would like to compare every instance between two data sources. In my real cases, I'm trying the compare the datetime fields,

             

            [No. of Achievement] (Aggregated)

            if  DATEDIFF('minute',ATTR([Expected Date Time]),ATTR([Other Data Source].[Actual Time])) < 0

            //AND some condition checking

            then 1

            else 0

            END

             

            [% Achievement Rate] (Can't use SUM to Aggregate again)

            SUM([No. of Achievement])/SUM(Fulfilled Condition Records)

             

            Because the data sources come from different databases (Oracle, mySQL) and Excel, I can't join the data directly. In my case, should I use the data integration tools to migrate the records into single database before continue the Tableau report development?

             

            Thanks!

            Marcus

            • 3. Re: Sum() the Attr() in calculated field from two data sources
              Carl Slifer

              I'd be putting them in the same database if possible. Then life will be

              much easier. As you can join and run similar calculated fields to make the

              pain go away.