Diagonal sums over flat list dimension on Anaplan

visivasa

Hello,

 

To put the problem simply, I need to know how to do the diagonal sums of an array in Anaplan.

 

To explain in detail: I have a module called "Daily Matrix". It has the dimensions: Time, Days after Registration (a List, that "Applies to" the entire module), and one of the line items in this module is a "Daily Rate %" (% formatted data point).

 

Days after Registration (members are 0-1000) is a list that I am using for custom time calculations. 

 

The module looks like: 

 

                                  0    /    1    /   2    / 3    / 4 ... 

(Model Time dim)

Jan 1                       1% / 2% / 3 %/ 2% / 1%

Jan 2                       1% / 2% / 1 %/ 1% / 1%

Jan 3                       1% / 3% / 1 %/ 2% / 1%

 

 

Now, I need diagonal sums, by day. i.e., for Jan 3, I need 1% + 2% (Jan 2, 1 day after registration) + 3% (Jan 1, 2 days after registration) = 6%.

 

In other words, this is a diagonal sum for each day. If you do a diagonal sum, you would get the % contribution from the "cohort" Jan1, and Jan 2 as well, that will contribute to Jan 3. 

 

If you search for diagonal sum on Excel, you will get array calculations. What I am trying is methodologically the same.

 

Can someone please help? Attached are images of the module where I have colored cells indicating the calcuations which shows this clearly.

 

DavidSmith

@visivasa

You can't do a diagonal calc as such, but you can use your 0-1000 list as the "fake" timescale.

I would advise putting in a check in the calculation to prevent the calculation running when it doesn't need to, so I have included how to do that here as well

 

1. Time Lookups

Create a module to calculate the start of the current year; I've assumed you only want the current year forwards, but this can obviosuly be changed if needed using ADDYEARS().  There is not timescale or other dimensions needed

 

2. I've used a 0-10 list for brevity.  Create a line item in a module just dimensioned by this list to hold the offset values (you may already have this, but it is more efficient to have this in it's own module) and turn the summary off!

 

3. Assume the same data as your example

 

4. Create a line item to calculate the offset date - START() - Assumptions.Value

 

5. Add the Diagonal Calculation to lookup the data using the above date:

 IF Offset day < Time Lookup.Current Year Start THEN 0 ELSE Data[LOOKUP: Offset day]

Note the check to ensure the calculation doesn't run unless needed

 

You will see the total now calculates the "diagonal sum"

6. You can reference this in other moduules using SELECT:

 

I would consider using a subset of the 0-1000 list if you don't need to go back that far, as this will improved the performance if the calculation module is as small as possible

 

I hope this solves the problem for you

David

visivasa

To clarify on the question, "Days after Registration" (0-1000) is treated as an "offset" to be added to each day. Thus Jan 1 + 10 (Days after Reg) = Jan 11.

DavidSmith

@visivasa

It is likely that you will need a module real time and fake time, but before I give any more advice, can you explain the significance of the 0,1,2, values for each day

 

Why does the value change in Jan-1 from 1% to 2% to 3% to 2% to 1% etc?

David

 

 

visivasa

Hi David,

 

Thanks for choosing to help me out.

 

The "Days after Registration" is the offset (0, 1, 2 and so on), or aging days from a particular point in time (say, Jan 1).

 

So, for Jan 1, an aging offset of 10 will be, Jan 11th. From Jan 1, every day's offset tracks the behavior of a variable indexed at Jan 1. 

 

 

Let us assume the no. of people who registered for a free trial subscription on Jan 1 is X. Now, for this "cohort", the offset days tracks for each day, how many of those X who signed up on Jan 1, decide to actually convert into paid subscribers as days go by. Therefore, Jan1 + 0 = Jan1 will have 1%. Whereas, Jan1 + 1 = Jan 2 will have 2% and so on. So the time series is built for every day and each day the % will vary. 

 

This is different from the % for the Jan2 time series because the Jan2 no. of customers will be Y and they will have a different behavior over time because they  may be exposed to different promotion campaigns or pricing and can have a different trajectory than X.

 

However, say, on Jan 5, you need to add Jan5 + 0 = Jan5's %, but also you had some customers subscribing on Jan5 that belonged to the Jan4th cohort (Jan4 + 1 offset day's % = Jan5's contribution from Jan4 cohort), and Jan3 (+2 days offset of Jan3rd's subscribers), Jan 2nd and Jan1st too. 

 

So for, Jan 5th, you need a diagonal sum from that matrix. The matrix has Jan1, Jan2 chorots on the rows, and offset days that track the curve for each day on the columns. 

 

Does this make sense? Apologies for the involved explanation.

rob_marshall

@visivasa,

 

I believe you will need to use the PROFILE function within Anaplan (https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/PROFILE.html).  Also, I did a quick search on AppHub for cohort, and found an Anaplan model that might help you, Planning, Budgeting, and Forecasting for SaaS.  I would recommend downloading that, looking though how they did it, specifically module SaaS Subscription Revenue Waterfall which is what I am thinking you are wanting.

 

Hope this helps,

Rob

visivasa

Hi Rob,

 

Thanks. The SAAS Model is huge for me to follow (I am a beginner) but I see the PROFILE() and other usages that I can investigate and try to understand. 

 

In the meanwhile, to make this entire thread simple: can anyone point a formula to build a diagonal sum? I have everything else in place for cohort based revnue subscription modeling.

 

Eg: 

      1 /  2 / 3

 

  1  0.5 / 0.5 / 0.5 

  2  1   / 2   / 1

  3   1 / 3   / 0.5

 

The diagonal sum for Row 3 = 1 + 2 + 0.5 = 3.5 

 

 

 

visivasa

David,

 

 

Thanks a lot! It worked!

 

I was adding the fake time list offset days to the current day and hence could not get the correct logic. Subtracting it was all that was needed. This exercise was eye opening 🙂

 

 

 

MikeWahl

David,

 

Thank you so much for helping out with this.  I love our Community!!!

 

Thank you.... Mike.