9 Replies Latest reply on May 3, 2018 10:33 AM by Jyothisree Rayagiri

    Cumulative sum for N observations

    Mayank Rustagi

      Hi,

       

      I have 1000 records (at daily level) with sales information. I want to create a field that is sum of that observation sales and next 4 observation sales. How can this be done in Tableau?

       

         

      DaySalesDesired field
      1 $ 5,153 $ 26,867
      2 $ 6,723 $ 30,834
      3 $ 2,282 $ 31,573
      4 $ 8,293 $ 32,406
      5 $ 4,416 $ 26,895
      6 $ 9,120 $ 26,108
      7 $ 7,462 $ 16,988
      8 $ 3,115 $ 9,526
      9 $ 2,782 $ 6,411
      10 $ 3,629 $ 3,629
        • 1. Re: Cumulative sum for N observations
          Jyothisree Rayagiri

          You can use lookup function to find the next 4 values.

           

           

          if last()>=4 then
          sum([Sales])+lookup(sum([Sales]),1)
          +lookup(sum([Sales]),2)
          +lookup(sum([Sales]),3)
          +lookup(sum([Sales]),4)

          elseif last()=3 then
          sum([Sales])+lookup(sum([Sales]),1)
          +lookup(sum([Sales]),2)
          +lookup(sum([Sales]),3)

          elseif last()=2 then
          sum([Sales])+lookup(sum([Sales]),1)
          +lookup(sum([Sales]),2)

          elseif last()=1 then
          sum([Sales])+lookup(sum([Sales]),1)

          elseif last()=0 then
          sum([Sales])

          end

           

           

          Maybe the calculation can be made simpler using windows functions but I haven't tested it.

          2 of 2 people found this helpful
          • 2. Re: Cumulative sum for N observations
            Jim Dehner

            Good morning

            one way is to use this formula

             

            it will return this

             

             

            I would also suggest in your real data you use a real date format for you day field

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            2 of 2 people found this helpful
            • 3. Re: Cumulative sum for N observations
              Tushar  More

              Hi  Mayank,

               

              Please check the attached workbook v10.3.

               

              Hope this helps.

               

              ~Tushar

              1 of 1 people found this helpful
              • 4. Re: Cumulative sum for N observations
                Mayank Rustagi

                Thanks folks (Tushar More Jim Dehner Jyothisree Rayagiri manish nigam Dinesh Kumar) for a prompt response. I tried using window_sum or the lookup function and it worked but it fails when I perform further aggregation.

                 

                Let me explain my business problem in detail -

                 

                Data Layout

                I have trading daily data for various stocks. These stocks can be identified by identifier ID. Data is only present for trading days i.e. Saturday, Sunday and other public holidays are excluded. For each record, I have net money coming in to or going out of that stock. Input spreadsheet is attached.

                 

                Business objective

                Determine 90th Percentile (user determined) worst out flow of 5 consecutive day period. If there are 200 days of trading days for a particular stock, create flows for current date and 4 future trading dates i.e. trading day 1-5, 2-6, 3-7, 4-8 .... 196-200, 197-200,198-200,199-200,200 form each 5 consecutive trading day period. From these 200 five consecutive day periods, filter out records that are in net negative (100,200,300,400,-1500 is included while -100,-200,-300,-400,+1500 is excluded). Apply percentile on remaining 5 consecutive day periods (can use parameters here so they are user defined P90, P95, P99 etc.)

                • 5. Re: Cumulative sum for N observations
                  Jyothisree Rayagiri

                  Hi Mayank

                   

                  Can you attach your workbook to show how far you have succeeded and we can help you where you are having trouble

                   

                  J

                  • 6. Re: Cumulative sum for N observations
                    Tushar  More

                    Mayank,

                     

                    I think this completely a new question. Please consider closing this thread and start a new one.

                    • 7. Re: Cumulative sum for N observations
                      Donna Coles

                      Ok - This thread has confused me - I didn't realise there were multiple people now asking questions on the same thread.  This is going to be difficult to manage and for anyone to get clarity on the problem.

                       

                      Mayank Rustagi - You were the original poster of the question.  It looks like your original question was answered by 3 different people.  If this is the case, please mark the most relevant answer as correct to close this thread down.  It seems though, that it didn't resolve your ultimate goal, as you didn't state that in your original query.  Can I request that, if you're still looking for input on this secondary query, you branch your post from 30th April into a new thread (see the Actions below the Correct Anwer button).

                       

                      dinesh kumar - Thank you for taking the time to post a workbook, but I'm a bit confused.  Do you know Mayank and are continuing this thread on his behalf (so you are completely aware of all his requirements and goal), or are you just trying to help out?  If it's the former then it would have been useful to state that.  If it's the latter, then you need to be careful doing this, as people may spend time helping you with what you think is the requirement, but ultimately it should be Mayank who should be providing the input.  It might be he's given up on the issue now.  My comments above in respect of how you use the @Mention facility still stands though.

                       

                      Regards

                      Donna

                      • 8. Re: Cumulative sum for N observations
                        Mayank Rustagi

                        Hi Donna,

                         

                        We (I and Dinesh) are working as a team and should have mentioned that. Perhaps, we are very new to the tool as well as the community.

                         

                        Thanks everyone for their support. I will create a separate thread for the follow up question.

                         

                        Thanks!

                        Mayank

                        • 9. Re: Cumulative sum for N observations
                          Jyothisree Rayagiri

                          Thanks Mayank

                          And like Donna said, the thread had become confusing.

                          I saw that Dinesh attached a workbook and thought he has put in a lot of work already and might have been very close to the solution.So, I did not take it forward.

                           

                          J