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