2 Replies Latest reply on Feb 28, 2011 11:13 AM by . Alena

    projection calculations

    . Alena

      Hello, please help

      I am trying to create a formula for weekly projected revenue number.

      example :

      1 week = 100,000

      2 week = 150,000

       

      I need to display week 3, 4 etc on a report as a running total of weeks multiplied by number of weeks in a month - therefore "projecting revenue".

      so the formula for

      week 1 would look like 100,000 *4 (4 weeks in January)

      week 2 would look like (100,000 + 150,000)/2*4 (4 weeks in January)  etc.

      I tried to add weekly revenue running total calculation and after that using "If" function to see what week it is for :

      If Week = 1 then RunningTotal*4

      elseif week = 2 then runningTotal/2* 4 etc

      But non-aggregate and aggregate do not work together. CASE statement gave me same error.

      Any ideas how to tackle this?

      Thank you much!

        • 1. Re: projection calculations
          Joe Mako

          How would you determine how many weeks are in a month? (or what would be the logic behind how you would expect it to be calculated, or is there a standard?)

           

          Once you know where your number of weeks in a month number is coming from, you would need a data table with every data you wan to forecast for. Tableau cannot generate rows, but if you give Tableau a row, Tableau can calculate just about any number and place it on that row. So with those things, yes, what you are asking for is very doable. Can you provide some sample data that represents the situation you are dealing with?

          • 2. Re: projection calculations
            . Alena

            Thank you Joe!

            Im attaching a workbook and data source (I used staged excel data to show example). I think my problem is not in number of weeks. Say - 4 weeks is a standard. My problem is that formula is looking at current week total revenue, instead of Month to date for every next week when I calculate run rate (resetting MTD number each month). So if it is week 1 - Run rate is week 1 revenue *4 (weeks), when week 2 - it is adding up week 1+2 / 2 * 4. As soon as I add aggregate to if statement - formula complains about mixing aggregate and non-aggregate. I thought of solution by adding Weekly Revenue calculations formulas - therefore adding 52 formulas for each week. But I am wondering if there is other simple solution for this run rate calculation. Thank you!