0 Replies Latest reply on Apr 10, 2013 6:52 AM by Greg Blackshields

    Projection Based on QTD moving Avg not correct when drilling down multiple levels

    Greg Blackshields

      Hi, i am currently facing a preplexing issue in which I use a QTD avg, based on revenue booked/days gone in QTR, multiply this result by the days left in the QTR to geta  base projection and then add this to the QTD revenue to get a Projection for the QTR.


      This works fine at 3 levels of drill down, but falters at the 4 th level, but interestingly enough at the 4th level of drill down, there are 9 sub totals, for 5 of these the projection does not work, for 4 it does.


      Below is a high level out line of the table calcs and the process flow for these in my report:using Tableau v7

      table alcuations
      QTD Avg               sum([Revenue])/max([DaysGoneInQtr])
      Projection             [QTD Avg]*min([DaysLeftin QTR])
      Projected QTR      SUM([Revenue])+[Projection ]
      Process is as follows:
      Revenue is booked
      DaysGoneInQtr is updated with Days in QTR less Days Gone in QTR
      qtd Avg is updated based on QTD daily bookings
      Projection is updated based on QTDAvg * days left in QTR [ this has to be the number of days left, detail page I have has all days in the QTR but I only need the last one on the file, as is QTR Days less Days Gone = Days Left ]
      Projected QTR is then updated


      Attached is a excel file showing the drill downs that work and that do not.

      The issue as I see it is that the sub total I am using I assumed would be the sum of the rows above the line-  you will see in TAB 3 that for some the region sub total does equal all those above the line but for others the regions subtotal des not equal what is above the line!!!


      So I was hoping soemone could shed some light on the reasons why the subtotal would not '' work'' in some instances but does in the other and if there is a way I can fix this, that is without having to create a dimension and measure for all the drill down options I have with regards to Region/Country/Route/Channel