5 Replies Latest reply on Mar 13, 2013 9:56 AM by Dan Huff

    Moving Calculation with Different Formulas for Each Previous Rows

    sira.sasitorn

      Hello,

       

      I'm having trouble calculating this formula. There are 2 parts to this formula, so I'll try my best to explain it. Any help is much appreciated.

       

      For each Month/Year, there are multiple Effective Month/Year. For example, in April 2009, there are 4 effective dates which are Feb 09, Mar 09, Apr 09, and May 09 (see Picture 1).

       

      Question 1:

      If I want to know what is the total amount for April 2009, I have to take the amount from each effective month in April 2009, and multiply them with using the correct formula and logic (formula and logic is shown in the table below). For example, in each effective month, the amount will be calculated differently. Going back each effective month, the amount is multiplied by 1+.

       

      For the month of April 2009 the final total amount is 29:

       

      Year

      Month

      Effective Month

      Amount

      multiply

      Formula

      Result

      2009

      April

      April

      1

      X

      1

      1

       

      March

      3

      X

      2

      6

       

      February

      2

      X

      3

      6

       

      January

      4

      X

      4

      16

      Final Total Amount

       

       

       

       

      29

       

      I know using Moving Calculation and set the previous value works. But instead of summing the previous records, how do I multiply it with different values depending on how far back the month is and then sum it up?

       

      Question 2:

      If you look at the attached spreadsheet, the formula keeps going (incrementing by 1) until it hits the previous Month (column B). So from April, we cross back to March (highlighted in orange) and since this is in a previous month (column B), we reset the formula. The reason why we start at x1 again is because March is 1 month back from April, and Feb (green) is 2 months back from April, so we x2 (according to the formula). How do I implement this logic to the formula above?

       

      Any help/suggestion is appreciated. Thanks in advance!

        • 1. Re: Moving Calculation with Different Formulas for Each Previous Rows
          Dan Huff

          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 fields--your date that Year and Month come from and another for where you get effective month--this 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

            Hi Dan,

             

            Thanks for your response. I have attached the sample data along with the sample twbx file. Thanks!

            • 3. Re: Moving Calculation with Different Formulas for Each Previous Rows
              Dan Huff

              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

              • 4. Re: Moving Calculation with Different Formulas for Each Previous Rows
                sira.sasitorn

                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

                • 5. Re: Moving Calculation with Different Formulas for Each Previous Rows
                  Dan Huff

                  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