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
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