8 Replies Latest reply on Jan 28, 2013 9:46 AM by Kenneth Ho

    Question with reseting the quarterly allocation against monthly usage

    Kenneth Ho

      I ran into a problem that i am not sure if I can fix in Tableau or if I should ask the developer to get the info and resolve it from the database.  I am trying to calculate hours usage base on the allocation for the quarter. for instance, Q1 has 3 months, and we are on Feb now.  I was expecting the calculation to calculate the total alloacation of Q1 which is (jan, feb, and mar) let say 500, 500, 500 respectively.  by each month usage, i want that to decrease.  Like jan usage is 700, then on the graph, i want to show that Jan is 1500, feb bar is 800. so that it can raise alarm for the manager to know if they go over the allocation. The allocation and usage get reset at each quarter.


      after Q1, i want it to reset in Q2.  then it will do the same again.  because mgrs might want to look back at the previous quarter, so i try to use that time progress function in tableau where they can choose which quarter to look into. 


      I have included the image of the dummy data from the database.  Area is where i would want to put into the filter, the date column will be on time series, LE base and LE adjustment show add together to become LE allocation, and LE used is how much has been spent.  The last column called quarterly usage, that is the calculation i was talking about.  On 1st month, it's the total allocation of 3 months (1,2,3 or 4,5,6 or 7,8,9 or 10,11,12) subtract the 1st month usage. 2nd month will be total allocation of 3 months subtract the usage of 1st and 2nd month. 


      Please let me know the best way to get this done.  Thanks