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.
Best Answer
-
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]
for a final answer of
Hope this helps,
Rob
2
Answers
-
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
0 -
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.
0 -
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
1 -
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!0 -
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.
0 -
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
0 -
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.
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.
0 -
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
2 -
Thanks @rob_marshall !! I understood the System Time Module now. Just to confirm, though it is called System Time module it is not an inbuilt anaplan module (rather a model builder created module), correct?
This greatly helps me because I have several multiple fake time dimensions (because of the cohort modeling) which have multiple different offsets - say for example, in one case we lookback 365 days and I have a list of 0-365 there. In another case, we look back 1000 days!
Having a common time module and having these calculations does save a lot of redundant calculations. Thanks again.0 -
Yes, the "system" module is just a term we use but it could also be Structure. It is not inherit to they system at all, but helps with mapping, filtering, master/meta data of lists. The one above is based off of Time, where you can do the mapping to the Month Names as well as filter out all quarters or years. Another example could be on the base member of a Product Hierarchy where you are figuring out what the parents are (P3, P2, P1). If you have these in one module, all module builders will know where to get the data instead of recreating the wheel in their own modules.
Again, the main benefits are:
- Reduced dimensionality for calucaltions (having the calculation being kicked off more times than needed)
- Reduced redundant calculations (having the code of Product or the Time mapping to the Month Name list is done one time instead of many times)
- Auditability - makes it easier to find where the data is coming from than in serveral different modules.
- Scalability - helps the system scale when larger and larger data/list volumes occur.
Hope this helps,
Rob
2 -
Thanks for the info! Helps me a lot.0
-
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!
0
