3 Replies Latest reply on Oct 19, 2016 4:30 AM by Peter Meier

    How to calculate accrual and deferral for subscription based business

    Peter Meier

      Hi there

       

      I'm running a subscription business and would like to use tableau to chart accrual and deferral numbers (no native speaker here, so not sure if these are the right words but please check the description below, should make things clear).

       

      Let's say I have three subscriptions:

       

      Basically, the calculation should divide the price by the number of months and evenly distribute these values:

       

      If a subscription is canceled (see ID 2), the remaining amount should be accrued/deferred (again, is this the right wording?).

       

       

      I wonder if something like that is possible in Tableau?

       

       

      Thanks a lot, kind regards

      Peter

        • 1. Re: How to calculate accrual and deferral for subscription based business
          Peter Meier

          Hey, anyone? Happy for any hint.

           

          Thanks

          Peter

          • 2. Re: How to calculate accrual and deferral for subscription based business
            Shinichiro Murakami

            Tableau is not calculation oriented tool, so the step can be pretty complicated in this case.

             

            Let's say you have this data.  BTW, without End date, we cannot start....

                

            IDSub StartsSubCanceledSub DurationProd PrizeEnd Date
            112/3/201661806/3/2017
            210/10/20163/1/20171224010/10/2017
            38/9/2016122408/9/2017

             

            Pivot Data thru edit data source

             

             

            I could create table , but adding column total doesn't work with this approach.(But still my best at this point)

             

            [Start Date]

            {fixed[ID]:min(if [Pivot field names]="Sub Starts" then [Pivot field values] END)}

             

            [End Date]

            {fixed [ID]:min(if  [Pivot field names]="End Date" then [Pivot field values] end)}

             

            [Cancel Date]

            {fixed[ID]:min(if [Pivot field names]="Sub Canceled" then [Pivot field values] end)}

             

            [End or Canceled]

            if isnull([Cancel Date]) then [End Date] else [Cancel Date] end

             

            [Duratioin before Cancel]

            datediff('month',

            datetrunc('month',[Start Date]),datetrunc('month',[End or Canceled]))

             

            [Monthly Score]

            [Prod Prize]/[Sub Duration]

             

            [Score for Calc]

            if [Pivot field values]=[Start Date] then ([Monthly Score])

            elseif [Pivot field values]=[End or Canceled] then [Monthly Score]*-1

            end

             

            [Canceled Month Score]

            [Monthly Score]*([Sub Duration]-[Duratioin before Cancel])

             

            [Total Score]

            if attr([Pivot field values])=attr([Cancel Date]) then min([Canceled Month Score])

            else running_sum(sum([Score for Calc])) end

             

             

            Thanks,

            Shin

            2 of 2 people found this helpful
            • 3. Re: How to calculate accrual and deferral for subscription based business
              Peter Meier

              Hi Shin

               

              Thanks a lot for the hint! Will start with this one and check how it goes.

               

               

              Kind regards

              Peter