Contributor

Multiple lists as only dimensions and LOOKUP

Hi,

 

I wish to lookup each and every individual cell in a module that is dimensioned by 2 lists. No time dimension.

 

I have a Module "Sequencing" dimensioned by 2 lists - MonthNames and Offset.

 

Attached are screenshots of the two lists and the module. The module has one line item - a % number that determines for each month (cohort), a seasonality trend. That is - how that % is "spread" across the timeline (aging timeline determined by Offset). The "spread", over the Offset aging months timeline, varies depending on the cohort month. In effect, both the lists create a month x month matrix.

 

I wish to lookup the % values in a different module line item, called "Bookings". How do I do it? Screen shot attached. This module is also a matrix, with rows as "Time dimension" (monthly scale, i.e., cohorts) and columns as "Offset" list. 

 

I want to be able to do a calculation like: Bookings = ReservationSequencing.ResToBook %[LOOKUP: TimePeriod, LOOKUP: "monthly cohort" ]

 

Please help.

12 REPLIES 12
Community Boss

Re: Multiple non-time lists as dimension and LOOKUP

@visivasa ,

 

If I am understanding you correctly, you are wanting the percentages (ResToBook) in the last booking picture you attached?  If so, then no need for the lookups because they are already implied by the dimensionality you have for the module.  The formula would be Bookings= ResToBook.

 

Hope this helps,

 

Rob

Contributor

Re: Multiple non-time lists as dimension and LOOKUP

@rob_marshall 

 

How is that possible? The Bookings Module is dimensioned by TIME and Month Offset.

 

When I add the formula you mentioned, it is giving me the totals as per the attached screenshot whereas I had mentioned that my goal is to be able to address/pick up each and every cell from the "Sequencing" module based on the Bookings month, and Bookings TimePeriod Offset. I will be eventually using the % to perform other calculations with other modules to arrive at the final value of "Bookings" but referring the correct % values from Sequencing is one step in the formula. 

Certified Master Anaplanner

Re: Multiple lists as only dimensions and LOOKUP

Hi,

 

Your source module is at Month Name list dimension, target module is at Anaplan Time. To connect this,

Create an intermediate module to make a connection between Month Name list and Anaplan TIme Dimension as shown below.

 

Manually select the respective month Name from the dropdown for now.

 

Bookings = ReservationSequencing.ResToBook %[LOOKUP: Time Map.Month ]

 

 

Photo1.png.jpg

Photo1.png.jpg

 

~Vignesh

Community Boss

Re: Multiple lists as only dimensions and LOOKUP

@visivasa 

 

I see, I got confused as the last picture was Jan 17 while January in the first picture was just January.  You will need to create a mapping module then that ties the Fake Months to the the real Time.

 

Ok, here you go...You have to create a System Module that makes the link from "Real Time" to your Month Name list.

 

I have your exact same lists created with the same codes as well as you ResToBook module:

2019-03-26_14-57-15.png

 

Create a System Module, based off Time like the below:

2019-03-26_14-58-22.png

 

In the target module, your formula will be: Reservation Sequencing.ResToBook[LOOKUP: Sys Time Visivasa.Link to Month Name List]

2019-03-26_14-59-28.png

 

 

for a final answer of 

 

2019-03-26_14-59-38.png

 

Hope this helps,

 

Rob

Contributor

Re: Multiple lists as only dimensions and LOOKUP

Hi @VIGNESH.M

Thanks for your solution. I think @rob_marshall has also used a similar logic but since it is formula based we need not enter the mapping manually for the entire timescale (for several years) in order to map every time dim month to Listitem.

But the idea is the same or similar. Thanks to both of you!
Contributor

Re: Multiple lists as only dimensions and LOOKUP

By the way, I did not create an intermediate mapping module. Just created one extra line item that did FINDITEM(MonthNames, TEXT(MONTH(START()) - 1)) and used this for lookup. 

 

 

Community Boss

Re: Multiple lists as only dimensions and LOOKUP

@visivasa ,

 

For performance reasons, you should create the mapping table as it is only doing the finditem as well as the other formulas once per month (one year of data, 12 times, 2 years of data 24 times).  If you add the formula in the target module, then you are kicking that formula off for every month as well as every offset (1 year: 12 months x 12 offsets = 144 times, 2 years of data: 24 months x 12 offsets = 288 times) even though you don't need to nor should you.  

 

Please remember, your mapping or SYS modules are your friend.

 

Rob

Highlighted
Contributor

Re: Multiple lists as only dimensions and LOOKUP

@rob_marshall 

 

Agreed. However, I had to keep it as a line item for every Offset Day (fake time list) because I needed it to return me a different cohort for a different offset. 

 

This is again similar to "Cohort modeling" or "Diagonal sum" -- a thread that you may have been part of. 

https://community.anaplan.com/t5/Anaplan-Discussions/Diagonal-sums-over-flat-list-dimension-on-Anapl...

 

To understand: consider the "Sequencing" module. What it is effectively telling is that: For any customer arriving in January, after 0 or 1 or 2 or 3 ... months offset, x% of customers have created a booking (or checked out an e-commerce cart etc). Which is the purpose of the sequencing module.

 

Which means, January Cohort of customers will have 23% of the total customers converting only in February (if you refer the % I had given earlier in this question) and 15% in March. For March, one has to add 49% of March customers + 25% of Feb customers, and 15% of Jan customers. And so on. 

 

Therefore, for March, this line item at Month offset 0 will map to March, and Month offset 1 will map to Feb and Month offset 2 will map to Jan and so on. 

 

SO my cohort keeps changing per Month offset and those appropriate cohorts' % values are to be picked up appropriately to arrive at a diagonal sum or "cohort laddering " as I like to call it. 

 

 

Community Boss

Re: Multiple lists as only dimensions and LOOKUP

@visivasa ,

 

I think you may be missing my point.  I am advocating putting the finditem() and figuring out the mapping to the Month Name in system module rather than doing the formula in the module with both lists (time as well as month offset).  Yes, you still need the lookup in the Time by Month Offset module, but not the mapping of Time to Month Names.  If you look at my last picture above, it is resolving to the correct answer (same as yours), but in doing the mapping of the Time to Month Names in a system module, that formula is getting kicked off fewer times, especially when you add more years to the model, which makes the process perform much better. 

 

Additionally, by placing the mapping in a system module, other model builders may need the same mapping which can be easily found in a system module and not hidden away in a Calculation module, thus they will not have to recreate the same functionality.

 

Hope this helps,

 

Rob