2 of 2 people found this helpful
You can use lookup function to find the next 4 values.
if last()>=4 then
elseif last()=3 then
elseif last()=2 then
elseif last()=1 then
elseif last()=0 then
Maybe the calculation can be made simpler using windows functions but I haven't tested it.
2 of 2 people found this helpful
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
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.
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 -
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.)
data tableau.xlsx 692.2 KB
Can you attach your workbook to show how far you have succeeded and we can help you where you are having trouble
I think this completely a new question. Please consider closing this thread and start a new one.
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.
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.
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.