Register

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" ]

13 REPLIES 13
Moderator

## Re: Multiple non-time lists as dimension and LOOKUP

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

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 ]

~Vignesh

Moderator

## Re: Multiple lists as only dimensions and LOOKUP

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:

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

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

Hope this helps,

Rob

New Contributor

## Re: Multiple lists as only dimensions and LOOKUP

The screenprints are very helpful Rob! Sometimes it can be hard to visualize the solution by reading comments. Seeing the interface and how the formula connects are very helpful!

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.

Moderator

## Re: Multiple lists as only dimensions and LOOKUP

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.

Rob

Contributor

## Re: Multiple lists as only dimensions and LOOKUP

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.