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

Highlighted

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

 

16 REPLIES 16
Highlighted
Master Anaplanner/Community Boss

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

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

 

 

Highlighted

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

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

Highlighted
Master Anaplanner/Community Boss

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

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

 

anirudh_0-1592554959086.png

 

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

 

Highlighted

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

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

Highlighted
Master Anaplanner/Community Boss

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

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

anirudh_0-1593410567610.png

 

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

Highlighted

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

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

Highlighted
Master Anaplanner/Community Boss

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

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

anirudh_0-1593596122768.png

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

Highlighted

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

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

Master Anaplanner/Community Boss

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

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