Implement Start and End

Hi

I have a config module(Config Module1) that contains key-value pairs with start and end dates. For example

customer_id (format = text)	customer_name (format = list formula = finditem(list1, customer_id))	key	value	start	end
1	cust1	category	c1	March 01, 2022	March 05, 2022
1	cust1	category	c2	March 06, 2022
2	cust2	category	c1	March 01, 2022
1	cust1	key2	value2	date2
1	cust1	key3	value3	date3

And I have a module (Module1) with the following schema and sample data:

date (format = date)	customer_id (format = text formula = finditem(list1, customer_id))	metric1 (format = number)
March 01, 2022	1	101
March 01, 2022	2	102
March 02, 2022	1	103
March 02, 2022	2	104
. . .	. . .	. . .
March 05, 2022	1	106
March 05, 2022	2	107
March 06, 2022	1	108
March 06, 2022	2	109

Now I want to add a category column in Module1 and the value of this category column should be based on the value corresponding to key = 'category' and based on customer_id and the date in Module 1 should be between the start and end date of Config Module1. And the result should look like this:

date	customer_id	category	metric1
March 01, 2022	1	c1	101
March 01, 2022	2	c1	102
March 02, 2022	1	c1	103
March 02, 2022	2	c1	104
. . .	. . .		. . .
March 05, 2022	1	c1	106
March 05, 2022	2	c1	107
March 06, 2022	1	c2	108
March 06, 2022	2	c1	109

How can I implement this in Anaplan?

Accepted answers

All comments

M.Kierepka

Hi @mohitveer_sandhu,

Generally, depending on exact use case, dimensions in the model you use (I don't get it for Config Module, is it transactional list?) and how your lists look like, this is something that might work for you:

Add new line item to "Module 1" formatted as "value" list, dimensioned by date, customer and key (so subsidiary view), with formula like "IF 'Config Module1'.start<= START() AND START() <='Config Module1'.end THEN 'Config Module1'.value ELSE BLANK". Summary should be set to LastNonBlank, and you should have top level on your keys list.

mohitveer_sandhu

@M.Kierepka Module 1 is Transactional Data and is dimensioned on the keys list that contains the unique keys to identify each record separately as Module 1 has multiple records for a single day.

So I cannot change the structure of Module 1 and all I can do is add a new column to this module that will have the category.

Config Module1 is basically a configuration table with the exact same schema as mentioned.

Quick Links

Can you help?

Unanswered questions

AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
Using time for something not time related - special characters :)$
We had a recent challenge with the need to "clean" invoice data descriptions from special characters. This was done previously in SQL with the a long convoluted formula "REPLACE(REPLACE(REPLACE('Ana&plan', '&', '') that would have to edited by a developer in case any new special characters came through. Using the replace…