3 Replies Latest reply on Mar 14, 2012 12:02 PM by Richard Leeke

    Simple multiplication - Hours * Rates

      Hi!

       

      I guess I miss something essential here:

      I have a table A containing projects, usernames and hours coming from one datasource.

      I have a table B containing usernames and rates coming from another datasource.

       

      When I now try to create a new calculated field "Costs" which should do a simple multiplication of hours*rates, I end up in one of the following errors:

      Either:

      You cannot mix aggregate and non-aggregate arguments to function"

      Or:

      "All fields must be aggregate or constant when using table calculation functions from multiple datasources"

       

      I found some resources on tableausoftware.com which could help me understand the problem but unfortunately, they are restricted to limited access...

      Is there somewhere an example which shows how I can create a flexible calculated field for doing this kind of calculation accross two different datasources?

       

      Thanks for your help!
      Cheers

        • 1. Re: Simple multiplication - Hours * Rates
          Richard Leeke

          I assume you are blending your datasources with A as the primary and B as the secondary, joining on username.

           

          The issue is that with data blending, you can only reference aggregate measures from the secondary datasource (B). Given the restriction that all fields in a calculation must be aggregate, or all must be non-aggregate, you therefore also have to use an aggregate from (A) as well.

           

          You need to choose the appropriate aggregate for hours and rates. Depending how your data is structured, it could be that you only expect a single row per project and user in data source A and a single row per user in B. Or you might have multiple rows per user and project (weekly hours, say). Let's assume that you do have multiple rows per user/project in A, but only one row per user in B. Your calculation would be:

           

          [costs] = SUM([hours]) * ATTR([rates])

           

          Let me just explain what that ATTR() is about. If you genuinely only have one rate per user, you could use any of MIN(), AVG(), MAX() or SUM() and they would all give the same answer (and the same as ATTR()). But let's say somebody's rate changes and you don't know about it. Now they might have 2 rates defined. So your calculation can't possibly give the right answer unless you know which rate applies to which hours. But if you had used any of MIN(), AVG(), MAX() or SUM(), the calculation would still appear to give a plausible answer (even though the 4 functions would all give different results), and you would probably never know.

           

          But ATTR() is a special aggregate function which if there is only one answer just returns that answer, but if the answer is not unique it returns a special value (*) - which in turn causes the result of any calculations trying to use it to be displayed as '*'. So you immediately know that your assumption is no longer valid.

           

          Hopefully that makes sense.

          1 of 1 people found this helpful
          • 2. Re: Simple multiplication - Hours * Rates

            Hi Richard,

             

            thanks for this very detailed reply!

             

            "I assume you are blending your datasources with A as the primary and B as the secondary, joining on username."

            Yes, and I have only one rate per user in my secondary datasource. One user cannot have two or more rates, not in this case.

             

            What I am trying to achieve is, to do a join between my primary datasource and my secondary datasource right in Tableau. Using this method, I can use one main primary datasource (times) which is the same for all future reports and many different secondary datasources (rates) for many different reports depending on the projects which are displayed. So, I want to extend the primary table (times) directly in Tableau with the fields rate and costs.

            My primary datasource consists of username, project, mins. I need Tableau to gather the rate of a user and extend the table to username, project, mins, rate, costs(=mins*rates) so it looks like:

            username project mins rate costs

            username project mins rate costs

            username project mins rate costs

            ...

            ...

            I could also do the join in a previous step (like in a SQL view or Access database), but I would like to have a main datasource which I do not have to manipulate with rates, because then I would need as many different "primary datasources" as reports I have (because the rates of users can change depending on the projects which have to be displayed...)

             

            When i try the ATT method you have outlined in your post, it works fine for a report on user detail level. But as soon as I remove the username column, which leads to a list of projects and their hours and costs only, the costs column is marked with "*". I understand why, but I would need Tableau to handle also this case.

             

            I've added an example as attachment.

            The user detail view works fine, thank you for that. But the project detail view gives me no result.

            But I guess this will not be possible and I will have to aggregate the tables a step before Tableau (SQL or Access), is this correct?

             

            Thanks & Regards,

            Tom

            • 3. Re: Simple multiplication - Hours * Rates
              Richard Leeke

              No time to look at your example right now, but you should be able to achieve what you want using data blending by using table calculations - though these can be a bit tricky.

               

              Basically you need to keep user on the view, by putting it on Level of Detail and then use appropriate table calculation functions (probably WINDOW_SUM()) to aggregate over the users in a project.