9 Replies Latest reply on Jun 14, 2018 11:27 AM by Deepak Rai

    Lookup Running Sum

    Drew Upah

      Hi All -

       

      I have been having an issue getting this to work in Tableau and I was hoping someone get help me. Essentially I have a list of part numbers based on order date and how many of those parts are required.

       

      What I need to do is have essentially a running sum of what is required by part number (Goal in Tableau).

       

         

      OrderPart NumberDue DateRequiredConsumed Above
      A5810002/6/201790
      B6605102/6/201710
      C9716882/6/201740
      D6605102/6/201741
      E9716888/30/201744
      F5810008/31/201719
      G9716888/31/201718
      H9716888/31/201719

       

      This has a semi-urgent time frame so any assistance at all would be very helpful.

        • 1. Re: Lookup Running Sum
          Ken Flerlage

          You can do this by creating a quick calculation of "Running Sum" on SUM(Required), then editing the table calculation as follows:

           

           

          See the attached workbook.

           

          If this meets your needs, please mark this answer as correct. Otherwise, please let me know what I've missed.

          • 2. Re: Lookup Running Sum
            Deepak Rai

            Like This?

            • 3. Re: Lookup Running Sum
              Drew Upah

              I made some updates to the table. After talking to my boss he is wanting to see a "Consumed Above" value which adds the Required quantity of every time that part number occurs above the current part number. See new table.

               

              This is a little trickier which is why I was having some issues with it.

               

              Also, I added an order number bc each row is a different customer order.

              • 4. Re: Lookup Running Sum
                Drew Upah

                I made some updates to the table. After talking to my boss he is wanting to see a "Consumed Above" value which adds the Required quantity of every time that part number occurs above the current part number. See new table.

                 

                This is a little trickier which is why I was having some issues with it.

                 

                Also, I added an order number bc each row is a different customer order.

                • 5. Re: Lookup Running Sum
                  Deepak Rai

                  Sorry..Requirement is not Clear to me.

                  • 6. Re: Lookup Running Sum
                    Drew Upah

                    No problem, let me see if I can help. I would like the "Consumed Above" column to take a running sum of the required field for every row that the part number exists. For example in the first 3 rows, the value is 0 because they are the first instance of that part number. For Order D however, it is the second time that part 660510 has been ordered and the consumed above would be 1 because order B had 1 required. Does that help?

                     

                    OrderPart NumberDue DateRequiredConsumed Above
                    A5810002/6/201790
                    B6605102/6/201710
                    C9716882/6/201740
                    D6605102/6/201741
                    E9716888/30/201744
                    F5810008/31/201719
                    G9716888/31/201718
                    H9716888/31/201719
                    • 7. Re: Lookup Running Sum
                      Drew Upah

                      I actually figured it out but I need another table calculation get the 0 value if it is the first occurrence. Is there a way to see the background calculation for a Table Calculation at the deepest level so that I can use it in another formula?

                      • 8. Re: Lookup Running Sum
                        Drew Upah

                        I actually figured it out but I need another table calculation get the 0 value if it is the first occurrence. Is there a way to see the background calculation for a Table Calculation at the deepest level so that I can use it in another formula?

                        • 9. Re: Lookup Running Sum
                          Deepak Rai

                          Can you post the Solution, I was already Half way on this.

                          Thanks

                          Deepak