3 Replies Latest reply on May 15, 2018 5:34 PM by swaroop.gantela

    Accrued Calculation - Summation 2

    Hector Santos

      NOTE: I have something very very similar here > Accrued Running Total - Summation Calculation. The difference between this last ask and this new ask is that at the beginning of the new year 2018 it will disregard any hours saved in 2017.   What this question is asking is how do I capture the total monthly that we left off within 2017 as part of this new number (so taking XYZ + the new stuff added in 2018).

       

      Hello,

       

      I am struggling to figure out this problem and not sure how to even classify it, but here it goes. What I've been asked is to do an accrued total from month to month.  So any total from a previous month carries on to the next month indefinitely. Any subsequent month that a value is added will have the same properties.

       

      summ2-1.png

       

      Within my workbook, the data would technically be something like this:

       

      summ2-2.png

       

       

      The biggest challenge I'm trying to tackle is how to have the accrued value in 2018, still consider the continuing amounts in the previous year (2017 values carry over but we don't want to add all the prior months, just start with 2017 values and new 2018 values). Another requirement is that I'll have to break it down to partial months.  So what I began doing is taking the hours down to a daily amount and using a datediff to develop a constant (the number of days the value has been in production).

       

      The purpose of this is to capture total saved hours with automation.  The automation tool is put into production, but every month the hours saved continue on to infinity.  We are trying to capture the total hours of automation hours saved. My idea was trying to develop a formula to get a factor (in the example above is the number of whole months, but I think days may work better) and then multiply the month by the factor and add it to all other months in that year. Any ideas on how to accomplish this would be greatly appreciated.

        • 1. Re: Accrued Calculation - Summation 2
          swaroop.gantela

          Hector,

           

          This may not at all be feasible for your setup,

          but I made a new datasource of just your aggregated values,

          and joined it to itself, and was able to in a few steps recreate your table above.

           

          The feasibility of this method may depend on your type of datasource

          and the amount of data that you have. If the aggregation could be done

          in the datasource, that would make it easy. Alternatively, this form of

          aggregation sounds like it would be a good task for Tableau Prep.

           

          I didn't explore, but there may be capability of doing the self-join

          with the disaggregated data, just requiring a few more filters.

           

          269676accrue.png

          • 2. Re: Accrued Calculation - Summation 2
            Hector Santos

            So I was able to get very close to what I'm aiming for in this solution.  And I think you have helped me get there.  Please see below for final result that is needed:

             

            SummationENDResult2.png

             

            So when I select the ALL parameter, I'd like for the running calculation (Accrual to be off that grand total row).  If they select 2017, that calculation is perfectly fine because that would just cut off where we need it.  Where it gets tricky is when it is 2018 parameter, they want whatever the Accrual to start fresh.  So you can see 2 scenarios can happen, if they are looking at the overall accrual it will be in Red (accruing from 2017 through current date 2018), but if they select 2018 it will begin the accrual from Jan of 2018.  So all the roll over numbers would still be added, just nothing accrued from 2017.

             

            Right now I'm able to get the numbers on the diagonal but not to replicate from month to month.

             

            summation2prog1.png

            Anymore thoughts?  Also, I have added the datasource if you need it.

             

            EDIT: Updated calc screenshot to match this datasource.

            • 3. Re: Accrued Calculation - Summation 2
              swaroop.gantela

              Hector,

               

              Thank you for the update.

               

              I think it is possible, but I'm not sure how well it will perform with your full dataset.

              It does use a self join. I also added a unique row number to each row.

               

              In the sheet named "parameter" in the workbook attached to the Forum thread,

              it employs two different runningsums, one that is calculated without restarting by year,

              and the other that does:

              IF [Select Timeframe]="All" THEN [RunningSum1]

              ELSEIF [Select Timeframe]="2017" AND YEAR(MIN([Date2]))=2017 THEN [RunningSum Restart]

              ELSEIF [Select Timeframe]="2018" AND YEAR(MIN([Date2]))=2018 THEN [RunningSum Restart]

              END

               

              The MonthlySum1 Values are calculated with:

              { FIXED [Date1 Month]:SUM(IF [Row1]=[Row 2] THEN [Estimated hours 1] END)}

               

              If these alterations are not feasible for your true dataset, will look into other methods.