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
Answers
-
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
0 -
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
0 -
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
2 -
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
0 -
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
1 -
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
0 -
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
0 -
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
0 -
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])0 -
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
0 -
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
0 -
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
0 -
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,
Anirudh0 -
-
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
0 -
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
0
