Diagonal sums over flat list dimension on Anaplan




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.


Best Answer


  • 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.
  • @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?




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

  • @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,


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



          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 




  • 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 🙂




  • David,


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


    Thank you.... Mike.