3 Replies Latest reply on Apr 18, 2014 10:50 AM by edgarernesto.hernandez

    SUMIF in Tableau

    Joan Gelpi

      Hi All,

       

      So I have two different data sets:

      (1)One data set with Supplier Name and Spend drilled down to each individual transaction

      (2)Another with the same Supplier Name, but a Spend Bucket category that sums the range of spend (e.g 0 to 5k, 100k to 250k)

       

      The overall goal is to have a calculated field that shows <250k Spend divided by Total Spend.

       

      In getting to the <250k Spend, I've tried creating calculated fields off the Spend tab using:

       

      IF ATTR([Spend Buckets (Spend Buckets.xlsx)].[2012 Grouping])='0 to 5k' THEN [TailSpend] ELSE '0' END

       

      Then I would have a Total Tail Spend calculated field summing all these buckets, but I'm getting the error: "Expected type float, found string. Result types from 'IF' expressions must match"

       

      Any suggestions? I've read through the forums, tried different calculations, but nothing has worked. This was the dumbed down idea where I could break down each bucket first and then add up all the buckets.

       

      Please Help!

        • 1. Re: SUMIF in Tableau
          edgarernesto.hernandez

          Remove the ' of the 0:

           

          IF ATTR([Spend Buckets (Spend Buckets.xlsx)].[2012 Grouping])='0 to 5k' THEN [TailSpend]

          ELSE 0 END

           

          And see if it works. Basically you're trying to tell Tableau, to return a numeric value but the '0' in between quotes is considered as a string by Tableau.

           

          If you're still getting the error make sure that the [TailSpend] field is considered by Tableau as a numeric value and not a string.

           

          To do so, right-click on the value in the Dimensions or Measures window, then go to Change Data Type and make sure Number is selected.

           

          You might also get an error about using only aggregates in the formula. I don't know if the [TailSpend ] field is a measure or a calculated field. But if you get that error, just replace it by SUM([TailSpend])

           

          Hope it helps.

          1 of 1 people found this helpful
          • 2. Re: SUMIF in Tableau
            Joan Gelpi

            Thanks Edgar. That definitely helped, but my next problem is adding all the buckets together where I'm using:

             

            SUM([0 to 5k]+[5k to 10k]+[10k to 20k]+[20k to 50k]+[50k to 100k]+[100k to 250k])

             

            But am getting the error: "Argument to SUM (an aggregate function) is already an aggregation and cannot be further aggregated.

             

            I've tried replacing the "+" with commas, but then I get the error message: Unknown function SUM called

            • 3. Re: SUMIF in Tableau
              edgarernesto.hernandez

              Since all the calculated fields are already a sum, you just need to remove the SUM in front of the calculation.

               

              Try only this:

               

              [0 to 5k]+[5k to 10k]+[10k to 20k]+[20k to 50k]+[50k to 100k]+[100k to 250k]

              1 of 1 people found this helpful