Implement Start and End

mohitveer_sandhu
mohitveer_sandhu
in Modeling

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))

keyvaluestartend
1cust1categoryc1March 01, 2022March 05, 2022
1cust1categoryc2March 06, 2022 
2cust2categoryc1March 01, 2022 
1cust1key2value2date2 
1cust1key3value3date3 

 

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, 20221

101

March 01, 20222102
March 02, 20221103
March 02, 20222104

.

.

.

.

.

.

.

.

.

March 05, 20221106
March 05, 20222107
March 06, 20221108
March 06, 20222109

 

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:

datecustomer_idcategorymetric1
March 01, 20221c1

101

March 01, 20222c1102
March 02, 20221c1103
March 02, 20222c1104

.

.

.

.

.

.

 

.

.

.

March 05, 20221 c1106
March 05, 20222c1107
March 06, 20221 c2108
March 06, 20222c1109

 

How can I implement this in Anaplan?

 

 

 

 

Tagged:

Answers

  • M.Kierepka
    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
    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.

Categories