
1. Re: Cumulative sum for N observations
Jyothisree Rayagiri Apr 25, 2018 2:53 AM (in response to Mayank Rustagi)2 of 2 people found this helpfulYou 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. Re: Cumulative sum for N observations
Jim Dehner Apr 25, 2018 3:35 AM (in response to Mayank Rustagi)2 of 2 people found this helpfulGood 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.

3. Re: Cumulative sum for N observations
Tushar More Apr 25, 2018 3:40 AM (in response to Mayank Rustagi)
moving calc.twbx 10.5 KB


4. Re: Cumulative sum for N observations
Mayank Rustagi Apr 30, 2018 6:59 AM (in response to 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 15, 26, 37, 48 .... 196200, 197200,198200,199200,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.)

data tableau.xlsx 692.2 KB


5. Re: Cumulative sum for N observations
Jyothisree Rayagiri Apr 30, 2018 7:04 AM (in response to Mayank Rustagi)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 May 2, 2018 6:23 AM (in response to Mayank Rustagi)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 May 3, 2018 12:11 AM (in response to Tushar More)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 May 3, 2018 2:14 AM (in response to Donna Coles)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 May 3, 2018 10:33 AM (in response to Mayank Rustagi)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