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

# Cumulative sum for N observations

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?

 Day Sales Desired 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

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

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

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

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.

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

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

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

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

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

Thanks Mayank