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

# How to calculate accrual and deferral for subscription based business

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

Hey, anyone? Happy for any hint.

Thanks

Peter

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

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

 ID Sub Starts SubCanceled Sub Duration Prod Prize End Date 1 12/3/2016 6 180 6/3/2017 2 10/10/2016 3/1/2017 12 240 10/10/2017 3 8/9/2016 12 240 8/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

Hi Shin

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

Kind regards

Peter