Create Forecast Model in Anaplan - Customer and SKU by different Unit

Hi,

I got some question about how to create a forecasting model in Anaplan by Customer and SKU level in different organization.

For example, I have 3 units (A , B , C) and they are selling to different customers and items.

How can I setup the relationship that if I select A and it only show customer 1 forecast item ABC and customer 2 forecast items BCD. No duplication cross the unit, customer and items.

I tried to pick up the Organization + Customer + Item dimension in a new model but the data is keep repeating even the customer not exist in that organization / the item not exist in that customer ...

I have attached a chart for better understanding. Since I got over 5,000 customer and 10,000SKU, it created a workspace issue to me as well if the data keep repeating. I am thinking I missed out some Parent relationship in between but doesn't got a clear idea how to proceed.

Thanks,

Kiwi

Capture_74139.JPG

Book2_74139.xlsx

Accepted answers

All comments

anirudh

Hi kiwi.chow@huhtamaki.com

You have to create a concatenated list in Anaplan. This technique is referred to by Anaplanners as reducing sparsity. Not only does your current approach show unnecessary combinations, it ends up consuming much more space than it needs to

You will have to follow these steps:

1. In your data source, generally data hub, create a Code line item (text formatted)

2. The code should be populated with the members just like the left portion of your attachment.

Unit-Customer-Item

India-Customer1-Item A

India-Customer2-Item B

The formula for the code will look something like:

IF VALUE <> 0 THEN NAME(ITEM(Country)) & "-" & NAME(ITEM(Customer)) & "-" & NAME(ITEM(SKU)) ELSE BLANK

The "ELSE BLANK" part of the formula is where the magic happens, and all the irrelevant combinations "disappear"

3. Create a numbered list in your model, and import these code combinations

4. Create a module dimensioned by the above list and store the values against each combination

5. Create filters, such as when you select A, the module shows the combinations with only Item A

These instructions are still too generic, if you can share a screenshot of data source, I can go into more detail

Regards,

Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

Thanks for your idea. Here is my screenshot and would like to have more advice from you.

Still duplicate, I think the formula should apply when I create rolling forecast model, by using this formula, connect the historical data into rolling forecast model ?

IF VALUE <> 0 THEN NAME(ITEM(Country)) & "-" & NAME(ITEM(Customer)) & "-" & NAME(ITEM(SKU)) ELSE BLANK

Thanks,

Kiwi

Capture2-Reporting_uni_74267.JPG

Capture1-combination_74267.JPG

Capture4-Customer_74267.JPG

Capture3-item_74267.JPG

anirudh

Hi Kiwi,

Good news!

The FACT01 SALES DATA module already contains the data in the format I was describing. That is, only necessary combinations are present.

There is no need to create the list and module I described in step 3 and 4 earlier

Create another module dimensioned by the same list as the FACT01 list. Create a text formatted property called FactCode with the formula - CODE(ITEM(Fact01 sales data list))

Then use formulas to get the units, customers and items out into 3 text formatted line items, one for each

Use LEFT, RIGHT, MID and the FIND formulas to extract the codes in the screenshot above into their respective line items

For instance, the formula for Unit would be LEFT(FactCode,1,FIND("_",FactCode))

Then create 3 list formatted items, one each for unit, cust and item. Fill them up with FINDITEM(dimension, code) formulas

The final step is applying a filter to show only the relevant combinations

Let me know if you need more help

Regards,

Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

Sorry to my late reply, just busy on other task last week. I am trying to do the step one by one.

I have created a new model to split unit, customer and item (Capture 1 & 2).

Sorry that I am not quite clear how to process the next step.

Then create 3 list formatted items, one each for unit, cust and item. Fill them up with FINDITEM(dimension, code) formulas

The final step is applying a filter to show only the relevant combinations

Should I create another 3 formatted line items in the same model ? Or I should create a new generate lines for customer / unit / item ?

I was thinking how it work of the combination. Beside item, I may also have another group related to the item / customer.

For example, (Capture 3)

unit is the lowest level but we have region in the upper level

item is the lowest level but SOP Family > SOP Sub Family is the upper level of item

Customer (Account) is the lowest level but Category and Key Accounts is the upper level.

Sometimes, people want to input the data in Key Account level instead of account level or SOP Sub Family level instead of item. How can we setup the filter for that ?

Maybe my question a bit complicated ...

BR,

Kiwi

Hi Kiwi,

I think I understood your questions. For a minute, put aside the questions related to capture 3 and let me clarify the steps I mentioned earlier:

> Then create 3 list formatted items, one each for unit, cust and item. Fill them up with FINDITEM(dimension, code) formulas

1. In the SALES01 ROLLING LIST, create 3 line items: Format them by Unit, Cust and Item lists. I will call these line items as Unit List, Cust List and Item List respectively

2. Apply formulas:

FINDITEM('O2 - Reporting Unit', FC_Unit)
FINDITEM('C3 - Account', FC_Customer)
FINDITEM('P3 - Item', FC_Item)

3. Exit blueprint, and make sure the the new line items are not blank. In other words, the list members must all be successfully found

> The final step is applying a filter to show only the relevant combinations

4. Create 3 modules and call them Unit Filter, Customer Filter, Item Filter. Dimension is O2, C3 and P3 lists. Each of these will have a boolean formatted line item with no formula (user input). I will call this line item as Filter

5. Go to the FACT01 SALES DATA module and create a line item called Dashboard Filter. Format is Boolean

6. Formula

(SALES01 ROLLING List.Unit List[LOOKUP: Unit List]) OR (SALES01 ROLLING List.Cust List[LOOKUP: Cust List]) OR (SALES01 ROLLING List.Item List[LOOKUP: Item List])

7. Publish your FACT01 SALES DATA module into a dashboard and in Data -> Filters, apply the Dashboard Filter

8. Also publish the 3 filter modules to the dashboard

9. Now, when a user opens this dashboard, user will use the checkbox on let's say only Unit #1 and after refreshing the dashboard, the FACT01 SALES DATA module will automatically show only the relevant combinations.

10. Since we added the Cust List and Item List as filters in the formula in step 3, all three filter checkboxes will work in conjunction!

Now, for the final requirement of having even the parents as filters (as per Capture 3), this is easily achievable with the setup I explained.

11. Simply create more line item items as explained in Step 1 and apply PARENT formulas. Ex: Create Region List, format by 'O1 - Region' and apply formula PARENT(Unit List). There is no need to do FINDITEM anymore

12. Create more Filter modules as explained in Step 4

13. Extend the formula as explained in Step 6. However, do be careful to ensure the Parent Filter modules take first precedence in the formula and then the Child Filter modules

14. Publish the additional filter modules to a dashboard

There are a few more points we can cover such as User based filters and dynamic cell access to block the selection of child filter modules if parent filter modules are selected. But first let me know if the above solutions works, and I can cover this part as well!

Regards,

Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

Much appreciated your detail guides.

When I FINDITEM, I got some error message there as attached. Is it any step I missed when I create the table ??

BR,

Kiwi

Capture1-Error_75623.JPG

Capture_Sales01_Rolling_75623.JPG

Capture-O2-Reporting_Unit_75623.JPG

anirudh

Hi Kiwi,

The lists are created fine, no need to make any changes there

The error is because the format of the line item is not changed to the list

In this capture, can you go to highlighted section and change the format to the Unit List, Cust List and the Item List respectively. The format should not be Text

Regards,

Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

Thanks and I can pass the FINDITEM issue. I am working on step 6 but seem something stupid mistake from my side again ...

I have created those Unit List, Cust List and Item List and it seem not able to pass the formula as you mention.

6. Formula

I think the source should be match but the system say no.

BR,

Kiwi

Hi Kiwi,

Looks like I gave you the wrong formula!
Can you try this formula instead for Step 6

(Unit Filter.Filter[LOOKUP: SALES01 ROLLING List.Unit List]) OR (Customer Filter.Filter[LOOKUP: SALES01 ROLLING List.Cust List]) OR (Item Filter.Filter[LOOKUP: SALES01 ROLLING List.Item List])

kiwi.chow@huhtamaki.com

Hi Aniruch,

Thanks, it seem more close to the end. I have created those things as you mentioned. However, those all three filter checkboxes seem not work in conjunction.

When I click Australia and refresh the table, the table can show only Australia data in the right hand side. When I click the Customer table like Singapore Customer, the data table still showing Australia. Is it some step is missing / wrong from my side ?

Also, how can we filter the customer filter when we select the Unit. Like we select Australia and the Customer and Item may only show the related combination.

BR,

Kiwi

Capture1_75956.JPG

anirudh

Hi Kiwi,

After clicking on Singapore Customer, try refreshing the dashboard.

If that doesn't work, could it be possible that there are no Singapore Customer combinations and hence the table is showing only Australia combinations?

The dashboard filter formula should ensure all filters work simultaneously...

Regards,

Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

Yes, I try to refresh the Singapore one but actually remain the same.

I did another test, I select Australia and just select some Australia Customer , but the full list of Australia customer still there after the refresh. (Pic: Capture 11)

I am guessing that is it the step 6 is not using "OR" function ? I have capture all my model setting for your information.

Hopefully get have more idea where is my mistake in the model : (

BR,

Kiwi

Hi Kiwi,

The model setup is exactly as expected. Can you show me the filters applied to FACT01 SALES DATA module in the last capture? Go to Data -> Filter

Regards,
Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

Attached is the screenshot of the Filter.

Thanks,

Kiwi

Capture_76167.JPG

anirudh

Hi Kiwi,

Looks like you've applied the filter only to the module
Instead, can you go to the dashboard and apply this filter to the module published in the dashboard

Regards,
Anirudh

kiwi.chow@huhtamaki.com

Hi Anirudh,

I have double checked my dashboard filter, this is same as what I did in the model but it doesn't work in conjunction.

I am thinking that should I change the formula in Dashboard Filter like Capture1 as attached ?

Change the OR into AND, it seem Unit Filter and Customer Filter will come up some relation. Isn't it ?

BR,

Kiwi

Capture_76689.JPG

Capture1_76689.JPG

Quick Links

Can you help?

Unanswered questions

Can Please explain the difference between a general list,a module,and action in Anaplan.
Can please explain why we create a general list ,a module,and Action ,and what purpose each one serves.🙂
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…