8 Replies Latest reply on Aug 8, 2016 5:26 AM by Michael Hesser

    SUMIF Calculation in Order to assign

    yassen.shahansky

      Hello,

       

      I have ran into an issue while putting together a new dashboard.

       

      Here is a mock up of my data.

       

      Order #     Item     $ Amount

        1               X              $50

        1               Y              $25

        2               X              $50

        3               Y              $25

       

      This is what I am trying to create in order to aggregate and view my data (distinct order #s only) based on predetermined order amount buckets ($0-25, $26-50, $51-75, $76-100, etc).

       

      Order #     Item     $ Amount     Total Order Amount (calculated field)        Order Size Bucket (calculated field)

        1               X              $50              $75                                                           $51-75

        1               Y              $25              $75                                                           $51-75

        2               X              $50              $50                                                           $26-50

        3               Y              $25              $25                                                           $0-25

       

      Obviously, in Excel, I would put this together with a SUMIF formula within a nested IF statement, but I am having a hard time in Tableau.

       

      Any insight would be greatly appreciated.

       

      Thanks

        • 1. Re: SUMIF Calculation in Order to assign
          Michael Hesser

          Hi Yassen;

           

          I think a LOD calculation will get you what you need.

           

          Total Order Amount

          {FIXED [Order #]:sum([$ Amount])}

           

          This should generate your total order amount, based on Order #.

           

          Your Order Size Bucket can be based off this new LOD calculation using nested IFs.

           

          When you are creating your view, place  [Total Order Amount] first so you can sort on it; you can hide it so people don't see it (this lets you get your Order Size Buckets to line up the way you want). Include it second time so your columns will look like:

           

          [Total Order Amount] [Order #] [Item] [$ Amount] [Total Order Amount] [Order Size Bucket]

           

          Do you think this will give you what you need?

          2 of 2 people found this helpful
          • 2. Re: SUMIF Calculation in Order to assign
            Derrick Austin

            Hey Yassen,

             

            I know it doesn't answer your question, but as an aside - You can replicate the "SUMIF" functionality of Excel by nesting an IF statement inside the SUM.

             

            SUM(IF [x] = 2 THEN [z] END)

            • 3. Re: SUMIF Calculation in Order to assign
              Jessica Last-Name

              LOD (Level of Detail) calculations are a very handy tool to have.

               

              Another option for the bins, if they are all going to be $25 in size is something like the following:

              '$' + STR(INT([Total Order Amount]/25) * 25 + 1) + ' - ' + STR(INT([Order Total]/25) * 25 + 25)

               

              An advantage here is that bins are automatically created for your data, so you won't need to keep adding nesting for bigger orders.

              Set this calculated field to a dimension and then you can easily make things like this:

              Screen Shot 2016-07-22 at 21.00.31.png

              If you want bins on a log scale, i.e. $1-9, $10-99, $100-999, try:

              '$' + STR(10^(INT(LOG([Order Total])))) + ' - ' + STR(10^(INT(LOG([Order Total])) + 1) - 1)

              and set the calculated field to a dimension as before.

               

              Hope this is of use!

              1 of 1 people found this helpful
              • 4. Re: SUMIF Calculation in Order to assign
                Michael Hesser

                Awesome point, Jessica! I don't use bins much, but this is a perfect use.

                • 5. Re: SUMIF Calculation in Order to assign
                  yassen.shahansky

                  Hi Michael,

                   

                  Yes, this was very helpful. Thank you!

                   

                  Obviously this is not my actual data set which is quite a bit more complicated; however, I was able to aggregate the $ Amount based on the Order # with the LOD calculation information you provided me with.

                   

                  One more question, is there a way to add additional level of detail using this calculation? For example, if I want to aggregate the $ amount based on the order # and then based on the Products within the Order #? I did some reading through the support section and was not able to get there.

                   

                  Thanks again!

                  Yassen

                  • 6. Re: SUMIF Calculation in Order to assign
                    yassen.shahansky

                    Hi Jessica,

                     

                    Thank you for your reply! I almost got there with the LOD calculation. Now I am just looking to add an additional level of detail/criteria based on which to aggregate the $ Amount. I forgot to mention this in my original post but the actual bins vary in size and there are only 5-6 of them. The data in my original post is completely made up to illustrate what I have in front of me and what I am looking to achieve. 

                     

                    Thanks!

                    Yassen

                    • 7. Re: SUMIF Calculation in Order to assign
                      Michael Hesser

                      Hello Yassen;

                       

                      Yes... all you need to do is add the field to a second LOD expression:

                       

                      {FIXED [Order #], [Item]:sum([$ Amount])}

                       

                      You might also be able to do this using Totals and Subtotals based on how your data is presented, but I'm a little more familiar with using LOD to get the values I need.

                       

                      Please let me know if this works for you!

                      1 of 1 people found this helpful
                      • 8. Re: SUMIF Calculation in Order to assign
                        Michael Hesser

                        Hello Yassen;

                        Was my answer(s) correct? If so, would you mark it as such? That way you won't have other people trying to answer your question and this post can be closed. I'll also get a little credit