
1. Re: Moving Calculation with Different Formulas for Each Previous Rows
Dan Huff Mar 12, 2013 3:25 PM (in response to sira.sasitorn)Is the data you attached the source data's format? If not, is there anyway you can post a small sample of the data in its original format?
This is difficult to answer without having a snippet of the actual data. I say this because if you have two date fieldsyour date that Year and Month come from and another for where you get effective monththis could be achieved with some logic and datemath in a calculation. However, if your fields are stored differently, it may be more difficult.
Let me know,
Dan

2. Re: Moving Calculation with Different Formulas for Each Previous Rows
sira.sasitorn Mar 13, 2013 6:43 AM (in response to Dan Huff)Hi Dan,
Thanks for your response. I have attached the sample data along with the sample twbx file. Thanks!

Test1.twbx.zip 23.1 KB


3. Re: Moving Calculation with Different Formulas for Each Previous Rows
Dan Huff Mar 13, 2013 8:40 AM (in response to sira.sasitorn)Sira
I have attached what I think is the answer you want. I changed your dates into date fields that Tableau can recognize as it makes the calculations much easier. If you right click on any of my calculations and click edit, you will see the formulas as well as some comments.
Hope this helps,
Dan
Test1  Dan.zip 52.4 KB


4. Re: Moving Calculation with Different Formulas for Each Previous Rows
sira.sasitorn Mar 13, 2013 9:36 AM (in response to Dan Huff)Hi Dan,
Thanks for the reply, this is very helpful. However, the factor calculation off. I attached the jpeg image of what the factor value needs to be. If we use April 08 as an example, the amount for the effective dates of March 08, would be multiply by 2 (in red) since April 08 is multiplied by 1 and March is a month before April.
The factor value, is based on [Month of Date] in relations to [Month of Effective Date]. So if our Date value is April 08, the factor value is based on what [Month of Effective Date] is. If [Month of Effective Date] is January, the factor value will be 4 since:
Jan = 4
Feb = 3
Mar = 2
Apr = 1 (multiple by 1 since our Date value starts on Apr)
And Dec would be 5.
Is it possible to increment it manually instead of using IF statements? The reason why I ask this, is because in the future, the Factor might not be increamenting by 1 anymore, but by random values depending on how far back the [Effective Date] is compare to the [Month of Date].
I hope this helps clarify the question. I really appreciate your help.
Thanks,
Sira

Picture 1.JPG 56.0 KB


5. Re: Moving Calculation with Different Formulas for Each Previous Rows
Dan Huff Mar 13, 2013 9:56 AM (in response to sira.sasitorn)I am following you now Sira. I missed that part about choosing a specific date to determine the base point. Attached is an updated version that deals with this case. As far as I can tell, my results match those in your update picture.
As for assigning the value arbitrarily in the future, this would (I suspect) have to be something that is done within the data itself. You may be able to extend the logic within the current calculation to address some of the case but it may end up being difficult due to the possible arbitrariness.
Hope this helps,
Dan

Test1  Dan V2.zip 53.7 KB
